Amazon and Microsoft asked these SQL questions in their interview.

 

1/ Find the second highest salary without using LIMIT, OFFSET, or TOP.

2/ Identify customers who made transactions in every month of the year.

3/ Calculate the 7-day moving average of sales for each product.

4/ Find users who logged in for at least three consecutive days.

5/ Use window functions to rank orders by order value per customer and return the top 3.

6/ Retrieve employees who earn more than their managers.

7/ Find duplicate rows in a large table and delete only the extras.

8/ Optimize a slow-performing query with multiple joins and aggregations.

9/ Get the first order for each customer, handling tie-breakers properly.

10/ Find products never purchased by any customer.

11/ Retrieve users who made purchases in 2 consecutive months but not the 3rd.

12/ Find the department with the highest total salary payout.

13/ Identify employees with the same salary in the same department.

14/ Calculate median salary without using built-in functions.

15/ Write a query to pivot rows into monthly sales columns.

16/ Identify top N customers with the highest total purchases (with tie handling).

17/ Get the running total of revenue per day using window functions.

18/ Find users whose most frequently purchased category in the last year is ‘Electronics’.

19/ Retrieve the first and last event for each user from an events table.

20/ Rank products by yearly sales, resetting rank every new year.

Solve these questions and test your SQL skills.

-- ------------------------------------------

-- SAMPLE DATASETS + SQL INTERVIEW QUERIES

-- For 2+ years experience (Amazon, Microsoft)

-- ------------------------------------------

-- ==========================================

-- TABLE 1: Employee (Q1, Q6, Q13, Q14)

-- ==========================================

CREATE TABLE Employee (

    emp_id INT PRIMARY KEY,

    salary INT,

    manager_id INT,

    dept_id INT

);

INSERT INTO Employee VALUES

(1, 100000, NULL, 1),

(2, 90000, 1, 1),

(3, 90000, 1, 1),

(4, 80000, 2, 1),

(5, 70000, 2, 2),

(6, 70000, 3, 2);

-- ==========================================

-- TABLE 2: Customers & Transactions (Q2)

-- ==========================================

CREATE TABLE Customers (

    customer_id INT PRIMARY KEY,

    name VARCHAR(100)

);

CREATE TABLE Transactions (

    txn_id INT PRIMARY KEY,

    customer_id INT,

    amount INT,

    txn_date DATE

);

INSERT INTO Customers VALUES (1, 'Alice'), (2, 'Bob');

INSERT INTO Transactions VALUES

(1, 1, 100, '2024-01-15'),

(2, 1, 200, '2024-02-15'),

(3, 1, 150, '2024-03-10'),

(4, 1, 175, '2024-04-20'),

(5, 1, 125, '2024-05-22'),

(6, 1, 100, '2024-06-01'),

(7, 1, 300, '2024-07-09'),

(8, 1, 180, '2024-08-13'),

(9, 1, 210, '2024-09-05'),

(10, 1, 130, '2024-10-29'),

(11, 1, 120, '2024-11-11'),

(12, 1, 250, '2024-12-10'),

(13, 2, 100, '2024-01-01'),

(14, 2, 200, '2024-02-15'),

(15, 2, 150, '2024-04-10');

-- ==========================================

-- TABLE 3: Sales (Q3, Q15, Q17, Q20)

-- ==========================================

CREATE TABLE Sales (

    product_id INT,

    sales_date DATE,

    qty INT

);

INSERT INTO Sales VALUES

(101, '2024-01-01', 10),

(101, '2024-01-02', 5),

(101, '2024-01-08', 8),

(101, '2024-01-09', 9),

(102, '2024-01-01', 2),

(102, '2024-01-04', 7),

(102, '2024-01-05', 6);

-- ==========================================

-- TABLE 4: Logins (Q4)

-- ==========================================

CREATE TABLE Logins (

    user_id INT,

    login_date DATE

);

INSERT INTO Logins VALUES

(1, '2024-01-01'),

(1, '2024-01-02'),

(1, '2024-01-03'),

(1, '2024-01-05'),

(2, '2024-01-01'),

(2, '2024-01-03'),

(2, '2024-01-04');

-- ==========================================

-- TABLE 5: Orders & OrderItems (Q5, Q8, Q9, Q10, Q12, Q16)

-- ==========================================

CREATE TABLE Orders (

    order_id INT PRIMARY KEY,

    customer_id INT,

    order_date DATE,

    amount INT

);

CREATE TABLE OrderItems (

    order_id INT,

    product_id INT,

    qty INT,

    unit_price INT

);

INSERT INTO Orders VALUES

(1001, 1, '2024-01-01', 200),

(1002, 1, '2024-01-05', 300),

(1003, 2, '2024-01-03', 400);

INSERT INTO OrderItems VALUES

(1001, 101, 1, 200),

(1002, 102, 2, 150),

(1003, 103, 4, 100);

-- ==========================================

-- TABLE 6: Products (Q8, Q10, Q18)

-- ==========================================

CREATE TABLE Products (

    product_id INT PRIMARY KEY,

    name VARCHAR(50),

    category VARCHAR(50)

);

INSERT INTO Products VALUES

(101, 'Phone', 'Electronics'),

(102, 'TV', 'Electronics'),

(103, 'T-Shirt', 'Clothing'),

(104, 'Headphones', 'Electronics'),

(105, 'Shoes', 'Footwear');

-- ==========================================

-- TABLE 7: Purchases (Q11, Q18)

-- ==========================================

CREATE TABLE Purchases (

    user_id INT,

    product_id INT,

    purchase_date DATE

);

INSERT INTO Purchases VALUES

(1, 101, '2024-01-15'),

(1, 102, '2024-02-15'),

(1, 103, '2024-04-10'),

(2, 101, '2024-01-01'),

(2, 105, '2024-03-01');

-- ==========================================

-- TABLE 8: Events (Q19)

-- ==========================================

CREATE TABLE Events (

    id INT,

    user_id INT,

    event_ts TIMESTAMP

);

INSERT INTO Events VALUES

(1, 1, '2024-01-01 09:00:00'),

(2, 1, '2024-01-05 10:00:00'),

(3, 2, '2024-01-02 14:00:00'),

(4, 2, '2024-01-04 16:00:00');

********************************************************************************

Solutions and Queries

***************************************************************************

1. Second Highest Salary (no LIMIT/OFFSET/TOP)

Table: Employee(id, salary)

sql

SELECT MAX(salary) AS second_highest
FROM Employee
WHERE salary < (
  SELECT MAX(salary)
  FROM Employee
);

Explanation: Find the highest salary in the subquery, then get the max salary below it.

---

2. Customers with Transactions Every Month

Tables: Customers(id), Transactions(id, customer_id, amount, txn_date)

sql

WITH months AS (
  SELECT 1 AS m UNION ALL SELECT 2 UNION ALL ... UNION ALL SELECT 12
)
SELECT c.customer_id
FROM (
  SELECT customer_id, EXTRACT(MONTH FROM txn_date) AS m
  FROM Transactions
  WHERE EXTRACT(YEAR FROM txn_date) = 2024
  GROUP BY customer_id, m
) AS cm
JOIN months USING (m)
GROUP BY customer_id
HAVING COUNT(DISTINCT m) = 12;

Explanation: Generate months 1–12, ensure each appears for each customer.

---

3. 7-Day Moving Average of Sales per Product

Table: Sales(product_id, sales_date, qty)

sql

SELECT product_id,
       sales_date,
       ROUND(
         AVG(qty) OVER (
           PARTITION BY product_id
           ORDER BY sales_date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
         ), 2
       ) AS moving_avg_7d
FROM Sales;

Explanation: Use window frame to average current + past 6 days.

---

4. Users Logged in for ≥3 Consecutive Days

Table: Logins(user_id, login_date)

sql

SELECT user_id
FROM (
  SELECT user_id,
         login_date,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
  FROM (
    SELECT DISTINCT user_id, login_date
    FROM Logins
  ) AS t
) AS x
GROUP BY user_id, login_date - (rn * INTERVAL '1 day')
HAVING COUNT(*) >= 3;

Explanation: Consecutive-date grouping using "gaps and islands".

---

5. Top 3 Orders by Value per Customer (Window Functions)

Table: Orders(order_id, customer_id, amount)

sql

SELECT order_id, customer_id, amount, rk
FROM (
  SELECT *,
         RANK() OVER (
           PARTITION BY customer_id
           ORDER BY amount DESC
         ) AS rk
  FROM Orders
) t
WHERE rk <= 3;

Explanation: Partition by customer, rank by order amount.

---

6. Employees Earning More Than Their Managers

Table: Employees(emp_id, manager_id, salary)

sql

SELECT e.emp_id, e.salary, e.manager_id, m.salary AS mgr_salary
FROM Employees e
JOIN Employees m
  ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

---

7. Find and Delete Duplicate Rows (Keep One)

Table: T(id, col1, col2, ...)

sql

WITH duplicates AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY col1, col2, col3
           ORDER BY id
         ) AS rn
  FROM T
)
DELETE FROM T
WHERE id IN (
  SELECT id
  FROM duplicates
  WHERE rn > 1
);

---

8. Optimize Slow Query with Joins + Aggregations

Scenario:

sql

SELECT o.customer_id,
       SUM(oi.qty * oi.unit_price) AS total
FROM Orders o
JOIN OrderItems oi USING(order_id)
JOIN Products p USING(product_id)
WHERE p.category = 'Electronics'
GROUP BY o.customer_id
HAVING SUM(oi.qty * oi.unit_price) > 1000;

Optimizations:

• Index on Products(category)

• Composite index on OrderItems(order_id, product_id)

• Rewrite using derived pre-agg:

  sql
  
  WITH elec AS (
    SELECT order_id, SUM(qty*unit_price) AS order_total
    FROM OrderItems oi
    JOIN Products p USING(product_id)
    WHERE p.category = 'Electronics'
    GROUP BY order_id
  )
  SELECT o.customer_id, SUM(e.order_total) AS total
  FROM Orders o
  JOIN elec e USING(order_id)
  GROUP BY o.customer_id
  HAVING SUM(e.order_total) > 1000;
  

---

9. First Order per Customer (with tie-breakers)

Table: Orders(order_id, customer_id, order_date, order_id)

sql

SELECT order_id, customer_id, order_date
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date, order_id
         ) AS rn
  FROM Orders
) t
WHERE rn = 1;

---

10. Products Never Purchased

Tables: Products(product_id), OrderItems(order_id, product_id, ...)

sql

SELECT p.product_id
FROM Products p
LEFT JOIN OrderItems oi
  ON p.product_id = oi.product_id
WHERE oi.product_id IS NULL;

---

11. Users Purchased in 2 Consecutive Months but Not 3rd

Table: Purchases(user_id, purchase_date)

sql

WITH up AS (
  SELECT user_id,
         EXTRACT(YEAR FROM purchase_date) AS yr,
         EXTRACT(MONTH FROM purchase_date) AS m
  FROM Purchases
  GROUP BY user_id, yr, m
),
dbo AS (
  SELECT user_id, yr, m,
         LAG(m) OVER (PARTITION BY user_id ORDER BY yr, m) AS prev_m
  FROM up
)
SELECT DISTINCT user_id
FROM dbo
WHERE
  (m = prev_m + 1)
  AND NOT EXISTS (
    SELECT 1
    FROM up u2
    WHERE u2.user_id = dbo.user_id
      AND u2.yr = dbo.yr
      AND u2.m = m + 1
  );

---

12. Department with Highest Total Salary

Table: Employees(emp_id, dept_id, salary)

sql

SELECT dept_id, SUM(salary) AS total_salary
FROM Employees
GROUP BY dept_id
ORDER BY total_salary DESC
FETCH FIRST 1 ROW ONLY;

---

13. Employees with Same Salary in Same Department

sql

SELECT dept_id, salary, COUNT(*) AS cnt
FROM Employees
GROUP BY dept_id, salary
HAVING COUNT(*) > 1;

---

14. Median Salary (no built-in)

sql

SELECT AVG(salary) AS median_salary
FROM (
  SELECT salary
  FROM (
    SELECT salary,
           ROW_NUMBER() OVER (ORDER BY salary) AS rn,
           COUNT(*) OVER () AS cnt
    FROM Employees
  ) t
  WHERE rn IN ((cnt + 1) / 2, (cnt + 2) / 2)
) x;

---

15. Pivot Rows → Monthly Sales

Table: Sales(product_id, sales_date, qty)

sql

SELECT product_id,
       SUM(CASE WHEN EXTRACT(MONTH FROM sales_date) = 1 THEN qty END) AS Jan,
       SUM(CASE WHEN EXTRACT(MONTH FROM sales_date) = 2 THEN qty END) AS Feb,
       ...,
       SUM(CASE WHEN EXTRACT(MONTH FROM sales_date) = 12 THEN qty END) AS Dec
FROM Sales
GROUP BY product_id;

Or use PIVOT in DBs that support it (e.g., SQL Server).

---

16. Top N Customers by Total Purchases (with ties)

sql

SELECT customer_id, total
FROM (
  SELECT customer_id,
         SUM(amount) AS total,
         DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dr
  FROM Orders
  GROUP BY customer_id
) t
WHERE dr <= 5;

---

17. Running Total of Revenue per Day

Table: Orders(order_id, order_date, amount)

sql

SELECT order_date,
       SUM(amount) AS daily_total,
       SUM(SUM(amount)) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM Orders
GROUP BY order_date
ORDER BY order_date;

---

18. Users’ Most Frequent Category = ‘Electronics’ in Last Year

Tables: Purchases(user_id, product_id, purchase_date), Products(product_id, category)

sql

WITH last_year AS (
  SELECT p.user_id, pr.category
  FROM Purchases p
  JOIN Products pr ON p.product_id = pr.product_id
  WHERE purchase_date >= CURRENT_DATE - INTERVAL '1 year'
),
freq AS (
  SELECT user_id, category, COUNT(*) AS cnt,
         ROW_NUMBER() OVER (
           PARTITION BY user_id
           ORDER BY COUNT(*) DESC
         ) AS rk
  FROM last_year
  GROUP BY user_id, category
)
SELECT user_id
FROM freq
WHERE rk = 1
  AND category = 'Electronics';

---

19. First & Last Event per User

Table: Events(id, user_id, event_ts)

sql

SELECT DISTINCT
       user_id,
       FIRST_VALUE(id) OVER (PARTITION BY user_id ORDER BY event_ts ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_event,
       LAST_VALUE(id) OVER (PARTITION BY user_id ORDER BY event_ts ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_event
FROM Events;

Or use ROW_NUMBER() twice:

sql

WITH numbered AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_ts) AS rn_asc,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_ts DESC) AS rn_desc
  FROM Events
)
SELECT user_id,
       MAX(CASE WHEN rn_asc = 1 THEN id END) AS first_event,
       MAX(CASE WHEN rn_desc = 1 THEN id END) AS last_event
FROM numbered
GROUP BY user_id;

---

20. Rank Products by Yearly Sales (Reset Annually)

Table: Sales(product_id, sales_date, qty)

sql

SELECT yr, product_id, total_qty,
       RANK() OVER (PARTITION BY yr ORDER BY total_qty DESC) AS yearly_rank
FROM (
  SELECT product_id,
         EXTRACT(YEAR FROM sales_date) AS yr,
         SUM(qty) AS total_qty
  FROM Sales
  GROUP BY product_id, yr
) t
ORDER BY yr, yearly_rank;

---

Summary Table

Here's a quick reference for techniques used:

Question	Technique(s)
1, 14	Aggregation + Subquery / Window Functions
2, 11	EXTRACT, date logic, CTE for consecutive periods
3, 7, 17, 20	Window functions
4, 19	Gap-and-island or window with row‑numbers
5, 16	RANK()/DENSE_RANK()
6	Self‑join
8	Indexing, refactoring, CTE optimization
9	ROW_NUMBER() with deterministic tie-break
10	LEFT JOIN ... IS NULL anti‑join
12, 13	Grouping + HAVING / ORDER BY
15	Conditional aggregation / pivot