12 SQL Interview Puzzles with Solutions

Indian data analyst interviews lean heavily on SQL, and the same handful of puzzle patterns appear over and over — at startups, service companies, and product firms alike. The goal is rarely the trick itself; interviewers want to see whether you reach for window functions, understand grouping, and can reason about edge cases. Here are twelve classics with clean, worked solutions and the reasoning behind each.

We will use two sample tables: orders(order_id, customer_id, order_date, amount, city) and employees(emp_id, name, dept, salary, manager_id). Solutions are written in PostgreSQL syntax.

1. Find Duplicate Rows

Find customers who placed orders with the exact same amount on the same day.

SELECT customer_id, order_date, amount, COUNT(*) AS dup_count
FROM orders
GROUP BY customer_id, order_date, amount
HAVING COUNT(*) > 1;

HAVING filters groups after aggregation. Any group with a count above one is a duplicate.

2. Delete Duplicates, Keep One

Keep the lowest order_id for each duplicate set.

DELETE FROM orders a
USING orders b
WHERE a.customer_id = b.customer_id
  AND a.order_date = b.order_date
  AND a.amount = b.amount
  AND a.order_id > b.order_id;

The self-join matches each row against an earlier twin and deletes the later one.

3. Second-Highest Salary

A perennial favourite.

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

The inner query finds the top salary; the outer finds the highest below it. This also handles ties gracefully. (A DENSE_RANK version generalises to Nth-highest.)

4. Nth-Highest Salary with DENSE_RANK

SELECT DISTINCT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) t
WHERE rnk = 3;   -- third highest

DENSE_RANK gives tied salaries the same rank with no gaps, so "third highest distinct salary" is exactly rnk = 3.

5. Top-N Per Group

Find the top 2 highest-paid employees in each department.

SELECT emp_id, name, dept, salary
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn <= 2;

PARTITION BY dept restarts the numbering per department. ROW_NUMBER breaks ties arbitrarily; use RANK if you want all tied employees included.

6. Running Total

Cumulative revenue by date.

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;

The nested SUM(SUM(...)) aggregates per day, then the window sums those daily totals cumulatively.

7. Month-over-Month Growth

Compare each month's revenue to the previous month using LAG.

WITH monthly AS (
    SELECT DATE_TRUNC('month', order_date) AS mth, SUM(amount) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    mth,
    revenue,
    LAG(revenue) OVER (ORDER BY mth) AS prev_revenue,
    ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY mth))
          / NULLIF(LAG(revenue) OVER (ORDER BY mth), 0), 1) AS growth_pct
FROM monthly
ORDER BY mth;

LAG pulls the previous row's value; NULLIF guards against dividing by zero.

8. Customers Who Bought in March But Not April

SELECT DISTINCT customer_id
FROM orders
WHERE order_date >= '2025-03-01' AND order_date < '2025-04-01'
  AND customer_id NOT IN (
      SELECT customer_id FROM orders
      WHERE order_date >= '2025-04-01' AND order_date < '2025-05-01'
      AND customer_id IS NOT NULL
  );

Note the IS NOT NULL guard inside the subquery — NOT IN breaks if it returns any NULL.

9. Consecutive Days (Gaps and Islands)

Find customers who ordered on 3 or more consecutive days.

WITH dated AS (
    SELECT DISTINCT customer_id, order_date,
           order_date - (ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date))::int AS grp
    FROM orders
)
SELECT customer_id, MIN(order_date) AS streak_start, COUNT(*) AS streak_len
FROM dated
GROUP BY customer_id, grp
HAVING COUNT(*) >= 3;

The trick: subtracting a row number from a date is constant within a consecutive run, so grp becomes a streak identifier.

10. Median Salary

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees;

PERCENTILE_CONT(0.5) interpolates the true median — cleaner than manual row-counting tricks.

11. Self-Join: Employees and Their Managers

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

A LEFT JOIN so the CEO (no manager) still appears, with NULL manager.

12. Pivot Orders by City Using Conditional Aggregation

SELECT
    DATE_TRUNC('month', order_date) AS mth,
    SUM(CASE WHEN city = 'Mumbai' THEN amount ELSE 0 END) AS mumbai,
    SUM(CASE WHEN city = 'Delhi'  THEN amount ELSE 0 END) AS delhi,
    SUM(CASE WHEN city = 'Pune'   THEN amount ELSE 0 END) AS pune
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY mth;

SUM(CASE ... END) rotates rows into columns — a portable pivot with no special syntax.

How to Practice These

• Understand, do not memorise. Interviewers tweak the table or twist the ask. If you grasp why ROW_NUMBER solves top-N, you can adapt it.
• Talk through edge cases. Ties, NULLs, empty groups, and gaps are where candidates separate themselves. Mention them before the interviewer asks.
• Master window functions. ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and running sums cover most of these puzzles.
• Watch the NOT IN trap. It is the most common silent bug; prefer NOT EXISTS when nulls are possible.

Drill these twelve patterns until the approach is automatic, and most interview SQL rounds will feel familiar rather than scary.

Related: SQL + Power BI Challenge for Data Analysts · Practise SQL problems

Don't just read. Prove your skill on DevWithData.