Scenario Based SQL Interview Questions (With Answers)

Q1: Find customers who have churned

The question: "Find customers who used to order regularly but haven't placed an order in the last 90 days."

SELECT customer_id, MAX(order_date) AS last_order_date
FROM orders
GROUP BY customer_id
HAVING MAX(order_date) < CURRENT_DATE - INTERVAL '90 days';

What a strong candidate does first: asks what "churned" means before writing anything — 90 days is a reasonable default, but a subscription business might define it differently than a grocery delivery app. Naming that assumption out loud is part of the answer.

Q2: Second-highest salary per department

The question: "Find the second-highest salary in each department."

WITH ranked AS (
  SELECT department, name, salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE rnk = 2;

The trap: using ROW_NUMBER() instead of DENSE_RANK(). If two employees are tied for the highest salary in a department, ROW_NUMBER() arbitrarily calls one of them rank 1 and the other rank 2 — incorrectly returning a tied-for-first salary as the answer to "second highest." DENSE_RANK() correctly gives both the tied top earners rank 1, so rank 2 is the true next-distinct value. Full comparison in ROW_NUMBER vs RANK vs DENSE_RANK.

Q3: The single biggest day-over-day sales drop

The question: "Which single day had the largest drop in revenue compared to the day before it?"

WITH daily AS (
  SELECT sale_date, SUM(amount) AS revenue
  FROM orders
  GROUP BY sale_date
),
with_change AS (
  SELECT sale_date, revenue,
    revenue - LAG(revenue) OVER (ORDER BY sale_date) AS change_from_prev
  FROM daily
)
SELECT sale_date, revenue, change_from_prev
FROM with_change
ORDER BY change_from_prev ASC
LIMIT 1;

LAG() pulls the previous day's revenue onto the same row, so the drop is a simple subtraction — sorted ascending, the most negative value is the biggest drop.

Q4: Users who upgraded then downgraded within 30 days

The question: "Find users who upgraded their subscription plan and then downgraded again within 30 days — a signal the upgrade didn't stick."

WITH upgrades AS (
  SELECT user_id, change_date AS upgrade_date
  FROM plan_changes WHERE change_type = 'upgrade'
),
downgrades AS (
  SELECT user_id, change_date AS downgrade_date
  FROM plan_changes WHERE change_type = 'downgrade'
)
SELECT DISTINCT u.user_id, u.upgrade_date, d.downgrade_date
FROM upgrades u
JOIN downgrades d
  ON d.user_id = u.user_id
 AND d.downgrade_date > u.upgrade_date
 AND d.downgrade_date <= u.upgrade_date + INTERVAL '30 days';

This is the same self-join-with-a-date-window shape used in strict, time-boxed funnel analysis — a pattern worth recognizing once, since it reappears across many "did X happen shortly after Y" business questions.

Q5: Detect likely duplicate (double) charges

The question: "Customer support suspects some customers were accidentally charged twice for the same purchase. Find likely duplicate transactions."

SELECT t1.transaction_id, t2.transaction_id AS possible_duplicate_id,
       t1.customer_id, t1.amount, t1.charged_at
FROM transactions t1
JOIN transactions t2
  ON t1.customer_id = t2.customer_id
 AND t1.amount = t2.amount
 AND t1.transaction_id < t2.transaction_id   -- avoid matching a row to itself / double-listing pairs
 AND t2.charged_at BETWEEN t1.charged_at AND t1.charged_at + INTERVAL '5 minutes';

Why t1.transaction_id < t2.transaction_id matters: without it, every duplicate pair would be listed twice (once in each direction) and every row would also match itself. This inequality is a small but common detail interviewers watch for.

Q6: Top 3 products per category by revenue

The question: "For each product category, show the top 3 best-selling products by revenue."

WITH product_revenue AS (
  SELECT category, product_name, SUM(revenue) AS total_revenue
  FROM sales
  GROUP BY category, product_name
),
ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY category ORDER BY total_revenue DESC
  ) AS rnk
  FROM product_revenue
)
SELECT category, product_name, total_revenue
FROM ranked
WHERE rnk <= 3
ORDER BY category, rnk;

This "top N per group" shape is one of the most reused patterns across analytics interviews — the same three lines (aggregate, rank with PARTITION BY, filter the rank) answer "top 3 per category," "top 5 per region," or "top 1 per customer" with no other changes needed.