SQL interview questions for data analysts, with answers.

1. Find delivered UPI orders from October 2026, newest firstEasy

Filter with WHERE, sort with ORDER BY. Use a half-open date range rather than MONTH(order_date) = 10: it reads the same, still works if the column is ever a timestamp, and lets the database use an index on order_date.

SELECT order_id, customer_id, order_date, amount
FROM orders
WHERE status = 'delivered'
  AND payment_mode = 'UPI'
  AND order_date >= '2026-10-01'
  AND order_date <  '2026-11-01'
ORDER BY order_date DESC, order_id DESC;

2. What is the difference between WHERE and HAVING?Easy

WHERE filters rows before they’re grouped. HAVING filters groups after aggregation, so it can use COUNT, SUM and the rest. Filter in WHERE whenever you can: fewer rows reach the grouping.

-- Cities with at least 2 delivered orders
SELECT c.city, COUNT(*) AS delivered_orders
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'delivered'     -- filters rows
GROUP BY c.city
HAVING COUNT(*) >= 2;            -- filters groups

3. COUNT(*), COUNT(column) or COUNT(DISTINCT column)?Easy

COUNT(*) counts rows. COUNT(referred_by) counts rows where that column isn’t NULL. COUNT(DISTINCT city) counts the different non-NULL values.

SELECT COUNT(*)             AS customers,       -- 7
       COUNT(referred_by)   AS were_referred,   -- 3
       COUNT(DISTINCT city) AS cities           -- 6
FROM customers;

4. Revenue and number of orders by payment modeEasy

Every column in SELECT must either be in GROUP BY or sit inside an aggregate. MySQL’s default ONLY_FULL_GROUP_BY mode and PostgreSQL both reject anything else.

SELECT payment_mode,
       COUNT(*)    AS orders,
       SUM(amount) AS revenue
FROM orders
WHERE status = 'delivered'
GROUP BY payment_mode
ORDER BY revenue DESC;

5. Why does WHERE referred_by = NULL return nothing?Easy

NULL means “unknown”, and comparing anything with unknown gives unknown, never true. Use IS NULL and IS NOT NULL. The same logic means WHERE referred_by <> 1 also skips the NULL rows; add OR referred_by IS NULL if you want them.

SELECT customer_id, name
FROM customers
WHERE referred_by IS NULL;     -- not: referred_by = NULL

6. How do you replace NULL with a default value?Easy

COALESCE returns its first argument that isn’t NULL. Keep the types the same: PostgreSQL won’t mix a number and text in one COALESCE. Here, customers with no delivered orders show 0 instead of NULL.

SELECT c.name,
       COALESCE(SUM(o.amount), 0) AS lifetime_value
FROM customers c
LEFT JOIN orders o
       ON o.customer_id = c.customer_id
      AND o.status = 'delivered'
GROUP BY c.customer_id, c.name;

7. Put each order into a size bandEasy

CASE checks its conditions top to bottom and stops at the first true one, so the order of the WHENs matters. To count orders per band, group by the same CASE expression.

SELECT order_id, amount,
       CASE
         WHEN amount < 1000 THEN 'Small'
         WHEN amount < 2500 THEN 'Medium'
         ELSE 'Large'
       END AS size_band
FROM orders;

8. What is the difference between DISTINCT and GROUP BY?Easy

DISTINCT removes duplicate rows from the result. GROUP BY collapses rows into groups so you can aggregate them. For a plain list of unique values they give the same answer; once you need a count or a sum per value, you need GROUP BY.

SELECT DISTINCT city FROM customers;

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city;

9. UNION or UNION ALL?Easy

Both stack two results with the same number of columns. UNION also removes duplicate rows, which costs a sort or a hash; UNION ALL keeps every row and is faster. Use UNION ALL unless you need the de-duplication.

SELECT name, 'customer' AS type FROM customers
UNION ALL
SELECT name, 'employee' FROM employees;

10. Primary key, unique key and foreign key: what’s the difference?Easy

A primary key identifies each row: unique, never NULL, one per table. A unique key also blocks duplicates, but allows NULLs, and a table can have several. A foreign key makes a column point at another table’s key, so you can’t record a payment for an order that doesn’t exist. order_items uses a composite primary key: (order_id, product_id).

CREATE TABLE payments (
  payment_id  INT PRIMARY KEY,           -- one per row, never NULL
  order_id    INT NOT NULL,
  upi_ref     VARCHAR(30) UNIQUE,        -- no duplicates, NULL allowed
  FOREIGN KEY (order_id) REFERENCES orders (order_id)
);

11. DELETE, TRUNCATE or DROP?Easy

DELETE removes the rows you choose and can be rolled back. TRUNCATE empties the whole table, fast. DROP removes the table itself. The dialect detail interviewers like: in MySQL, TRUNCATE causes an implicit commit and can’t be rolled back; in PostgreSQL it’s transactional and can be.514

-- Don't run these on your practice data
DELETE FROM orders WHERE status = 'cancelled';  -- fails here: order_items still points at order 1003
TRUNCATE TABLE order_items;                      -- every row goes, the table stays
DROP TABLE order_items;                          -- the table goes

12. The 5 most expensive products, and what about ties?Easy

LIMIT works in MySQL and PostgreSQL (SQL Server uses TOP). It stops at exactly five rows even if the sixth has the same price, so say that out loud. PostgreSQL can keep the ties with FETCH FIRST … WITH TIES; in either database, DENSE_RANK does it too (question 30).10

SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 5;

-- PostgreSQL, keeping ties for 5th place
SELECT product_name, price
FROM products
ORDER BY price DESC
FETCH FIRST 5 ROWS WITH TIES;

13. In what order does SQL run a query?Medium

Not the order you write it. Logically: FROM and joins, then WHERE, GROUP BY, HAVING, SELECT (window functions run here), DISTINCT, ORDER BY and finally LIMIT. That’s why an alias from SELECT works in ORDER BY but not in WHERE, and why you can’t filter on a window function without a subquery or CTE. PostgreSQL accepts aliases in GROUP BY but not in HAVING; MySQL accepts both.10

-- Fails: WHERE runs before SELECT, so "line_total" doesn't exist yet
SELECT order_id, quantity * unit_price AS line_total
FROM order_items
WHERE line_total > 2000;

-- Works: repeat the expression; ORDER BY runs after SELECT, so the alias is fine there
SELECT order_id, quantity * unit_price AS line_total
FROM order_items
WHERE quantity * unit_price > 2000
ORDER BY line_total DESC;

14. Explain INNER, LEFT, RIGHT and FULL OUTER joinsMedium

INNER JOIN keeps only rows that match on both sides. LEFT JOIN keeps every row of the left table, with NULLs where the right has no match; RIGHT JOIN is the mirror image, and most people rewrite it as a LEFT JOIN. FULL OUTER JOIN keeps everything from both. MySQL doesn’t have it, so combine a LEFT JOIN with the unmatched rows of a RIGHT JOIN.3

-- PostgreSQL
SELECT c.customer_id, c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id;

-- MySQL
SELECT c.customer_id, c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
UNION ALL
SELECT c.customer_id, c.name, o.order_id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

15. Find customers who have never orderedMedium

This is an anti join: rows in one table with no match in another. Two safe ways: a LEFT JOIN keeping the rows where the right side is NULL, or NOT EXISTS. Avoid NOT IN on a column that can hold NULL: if the subquery returns a single NULL, NOT IN returns no rows at all.

-- 1. LEFT JOIN … IS NULL
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

-- 2. NOT EXISTS
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- Both return customers 6 and 7

16. Why did my LEFT JOIN lose rows?Medium

A condition on the right-hand table in WHERE throws away the NULL rows the LEFT JOIN kept, so it behaves like an inner join. Put conditions on the right-hand table in the ON clause. Count o.order_id, not *, or customers with no orders show 1.

-- Wrong: customers without a delivered order disappear
SELECT c.name, COUNT(o.order_id) AS delivered_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'delivered'
GROUP BY c.customer_id, c.name;

-- Right: they stay, with 0
SELECT c.name, COUNT(o.order_id) AS delivered_orders
FROM customers c
LEFT JOIN orders o
       ON o.customer_id = c.customer_id
      AND o.status = 'delivered'
GROUP BY c.customer_id, c.name;

17. List each employee with their manager, and find who earns more than theirsMedium

A self join joins a table to itself under two aliases. Use a LEFT JOIN for the list, so the head of the company (no manager) still appears.

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

SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.emp_id = e.manager_id
WHERE e.salary > m.salary;
-- Kunal Verma (16 lakh) earns more than Ishaan Gupta (14 lakh)

18. Monthly revenue for 2026Medium

Cut each date down to its month, then group. Dates are where MySQL and PostgreSQL differ most, so learn both forms. Other pairs worth knowing: days between two dates is DATEDIFF(later, earlier) in MySQL and later - earlier in PostgreSQL; “30 days ago” is CURRENT_DATE - INTERVAL 30 DAY in MySQL and CURRENT_DATE - INTERVAL '30 days' in PostgreSQL. EXTRACT(MONTH FROM order_date) works in both.

-- PostgreSQL
SELECT DATE_TRUNC('month', order_date)::date AS month,
       SUM(amount) AS revenue
FROM orders
WHERE status = 'delivered'
  AND order_date >= '2026-01-01' AND order_date < '2027-01-01'
GROUP BY 1
ORDER BY 1;

-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m-01') AS month,
       SUM(amount) AS revenue
FROM orders
WHERE status = 'delivered'
  AND order_date >= '2026-01-01' AND order_date < '2027-01-01'
GROUP BY 1
ORDER BY 1;

19. Revenue by product categoryMedium

One order can hold products from several categories, so category revenue has to come from the order lines: join three tables and multiply quantity by the price paid.

SELECT p.category,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders   o ON o.order_id   = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status = 'delivered'
GROUP BY p.category
ORDER BY revenue DESC;

20. My revenue total is too high after a join. Why?Medium

Joining orders to order lines repeats each order once per line, so SUM(o.amount) counts a two-line order twice. This is called fan-out. Sum each table at its own grain: the order total from orders, or the line values from order_items. Don’t hide it with DISTINCT.

-- Wrong: orders 1001 and 1004 have two lines each, so they count twice
SELECT SUM(o.amount) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'delivered';

-- Right: the order table on its own …
SELECT SUM(amount) AS revenue
FROM orders
WHERE status = 'delivered';

-- … or the line-level values
SELECT SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'delivered';

21. Subquery or CTE? Find products priced above their category’s averageMedium

A subquery is a query inside another; a correlated subquery refers to the outer row. A CTE (common table expression) is a named result set defined with WITH, so the query reads top to bottom. Both give the same answer here; CTEs are easier to read and debug. CTEs need MySQL 8.0 or later, and PostgreSQL folds a CTE that’s used once into the main query, so there’s usually no speed cost.111

-- Correlated subquery
SELECT p.product_name, p.category, p.price
FROM products p
WHERE p.price > (
  SELECT AVG(p2.price) FROM products p2 WHERE p2.category = p.category
);

-- CTE
WITH category_avg AS (
  SELECT category, AVG(price) AS avg_price
  FROM products
  GROUP BY category
)
SELECT p.product_name, p.category, p.price
FROM products p
JOIN category_avg ca ON ca.category = p.category
WHERE p.price > ca.avg_price;

22. EXISTS or IN?Medium

Both test membership. IN compares a value with a list; EXISTS asks whether a correlated subquery returns any row at all, and can stop at the first. Modern optimisers often plan them the same way, so choose for correctness: NOT EXISTS is safe with NULLs, NOT IN isn’t (question 15).

-- Customers with at least one UPI order
SELECT c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id AND o.payment_mode = 'UPI'
);

SELECT name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE payment_mode = 'UPI');

23. Find duplicate customers by emailMedium

Group by the cleaned value and keep the groups with more than one row. Clean first: the same email can differ in case or carry a stray space. MySQL’s default collation already ignores case; PostgreSQL’s doesn’t, so LOWER(TRIM(…)) keeps the answer the same in both.

SELECT LOWER(TRIM(email)) AS email_clean,
       COUNT(*)           AS sign_ups
FROM customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;
-- aarav@example.com, 2 (customers 1 and 6)

24. What share of orders was cancelled in each city?Medium

Conditional aggregation: count only the matching rows with SUM(CASE …), then divide. Multiply by 100.0, not 100: in PostgreSQL an integer divided by an integer drops the decimals, while MySQL’s / keeps them.94

SELECT c.city,
       COUNT(*) AS orders,
       ROUND(100.0 * SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END)
             / COUNT(*), 1) AS cancelled_pct
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.city
ORDER BY cancelled_pct DESC;

25. Pivot revenue: cities as rows, payment modes as columnsMedium

Neither MySQL nor PostgreSQL has a PIVOT keyword, so a CASE inside an aggregate is the answer that works in both. PostgreSQL also offers FILTER, which returns NULL rather than 0 when nothing matches.

SELECT c.city,
       SUM(CASE WHEN o.payment_mode = 'UPI'  THEN o.amount ELSE 0 END) AS upi,
       SUM(CASE WHEN o.payment_mode = 'Card' THEN o.amount ELSE 0 END) AS card,
       SUM(CASE WHEN o.payment_mode = 'COD'  THEN o.amount ELSE 0 END) AS cod
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'delivered'
GROUP BY c.city;

-- PostgreSQL alternative for one column
-- SUM(o.amount) FILTER (WHERE o.payment_mode = 'UPI') AS upi

26. Customers who ordered in both September and October 2026Medium

Filter to the two months, group by customer and keep those with two different months. INTERSECT also works, in PostgreSQL and in MySQL from 8.0.31.6

SELECT customer_id
FROM orders
WHERE order_date >= '2026-09-01' AND order_date < '2026-11-01'
GROUP BY customer_id
HAVING COUNT(DISTINCT EXTRACT(MONTH FROM order_date)) = 2;
-- Customers 1 and 2

27. ROW_NUMBER, RANK and DENSE_RANK: what’s the difference?Medium

A window function calculates across a set of rows related to the current row and, unlike GROUP BY, keeps every row. The three ranking functions differ only on ties: ROW_NUMBER never repeats, RANK repeats and then skips, DENSE_RANK repeats without skipping. Don’t name a column rank in MySQL 8: it’s a reserved word.127

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees
WHERE department = 'Analytics';

-- name          salary   row_num  rnk  dense_rnk
-- Nisha Rao     3200000  1        1    1
-- Arjun Mehta   1800000  2        2    2
-- Priya Nair    1500000  3        3    3
-- Vikram Joshi  1500000  4        3    3
-- Farah Ali     1200000  5        5    4
-- Which of the tied pair gets row_num 3 is arbitrary: add a tie-breaker (ORDER BY salary DESC, emp_id)

28. Find the second-highest salaryMedium

A classic. Give more than one way, and say which edge cases each handles: ties at the top, and a table with only one distinct salary.

-- 1. The highest salary below the highest: NULL if there's no second
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- 2. DENSE_RANK: handles ties, and changes to the Nth by editing one number
SELECT DISTINCT salary AS second_highest
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

-- 3. LIMIT with OFFSET: needs DISTINCT, or a tie at the top breaks it
SELECT DISTINCT salary AS second_highest
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
-- All three return 1800000

29. The second-highest salary in each departmentHard

PARTITION BY restarts the ranking for each department. Change rnk = 2 to rnk <= 3 for the top three earners in each.

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;
-- Analytics: Arjun Mehta, 1800000 · Operations: Ishaan Gupta, 1400000

30. The top 3 products by revenue in each categoryHard

The pattern for any “top N per group”: aggregate, rank inside a PARTITION BY, then filter the rank in an outer query, because a window function can’t go in WHERE. Use ROW_NUMBER for exactly N rows, DENSE_RANK to keep ties, and say which you chose.

WITH product_revenue AS (
  SELECT p.category, p.product_id, p.product_name,
         SUM(oi.quantity * oi.unit_price) AS revenue
  FROM order_items oi
  JOIN orders   o ON o.order_id   = oi.order_id
  JOIN products p ON p.product_id = oi.product_id
  WHERE o.status = 'delivered'
  GROUP BY p.category, p.product_id, p.product_name
),
ranked AS (
  SELECT category, product_name, revenue,
         ROW_NUMBER() OVER (PARTITION BY category
                            ORDER BY revenue DESC, product_id) AS rn
  FROM product_revenue
)
SELECT category, product_name, revenue
FROM ranked
WHERE rn <= 3
ORDER BY category, rn;

31. Delete duplicate customers, keeping the first sign-upHard

First look at exactly what you’ll delete, then remove every row whose cleaned email matches a customer with a lower id. In real work, move the duplicate’s orders to the kept customer first, or the foreign key will stop you; mentioning that earns marks.

-- See what will go
SELECT c.*
FROM customers c
JOIN customers k
  ON LOWER(TRIM(k.email)) = LOWER(TRIM(c.email))
 AND k.customer_id < c.customer_id;

-- PostgreSQL
DELETE FROM customers c
USING customers k
WHERE LOWER(TRIM(k.email)) = LOWER(TRIM(c.email))
  AND k.customer_id < c.customer_id;

-- MySQL
DELETE c
FROM customers c
JOIN customers k
  ON LOWER(TRIM(k.email)) = LOWER(TRIM(c.email))
 AND k.customer_id < c.customer_id;

32. A running total of daily revenueHard

With ORDER BY and no frame, a window runs from the first row to the current row and includes ties (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Rolling up to one row per day first avoids a surprise: two rows on the same date would both show the larger total. Add PARTITION BY to restart the total, for example each month.2

WITH daily AS (
  SELECT order_date, SUM(amount) AS revenue
  FROM orders
  WHERE status = 'delivered'
  GROUP BY order_date
)
SELECT order_date, revenue,
       SUM(revenue) OVER (ORDER BY order_date) AS running_total,
       SUM(revenue) OVER (PARTITION BY EXTRACT(MONTH FROM order_date)
                          ORDER BY order_date) AS month_to_date
FROM daily
ORDER BY order_date;

33. A 7-day moving average of daily revenueHard

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW averages the last seven rows, not days: if some days have no orders, the window stretches past a week. Fix it with a calendar of every date (generate_series in PostgreSQL, a recursive CTE in MySQL), or with a date-range frame: RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW in MySQL, INTERVAL '6 days' in PostgreSQL. And the first six days average fewer than seven values: say so.2

WITH daily AS (
  SELECT order_date, SUM(amount) AS revenue
  FROM orders
  WHERE status = 'delivered'
  GROUP BY order_date
)
SELECT order_date, revenue,
       AVG(revenue) OVER (
         ORDER BY order_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS avg_last_7_rows
FROM daily
ORDER BY order_date;

34. Month-on-month revenue growthHard

LAG reads a value from the previous row, LEAD from the next. NULLIF(…, 0) turns a zero into NULL, so a month after a zero month shows NULL instead of failing: PostgreSQL raises an error on division by zero, while MySQL returns NULL. Mention gaps too: a month with no sales is missing, not zero, unless you add it.4

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date)::date AS month,  -- MySQL: DATE_FORMAT(order_date, '%Y-%m-01')
         SUM(amount) AS revenue
  FROM orders
  WHERE status = 'delivered'
  GROUP BY 1
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS growth_pct
FROM monthly
ORDER BY month;
-- October 2026: 12791.00 against 4297.00 in September, growth 197.7

35. Days between each customer’s consecutive ordersHard

LAG inside a PARTITION BY customer_id gives each order the date of that customer’s previous one. A customer’s first order has no previous one, so it shows NULL. Likely follow-ups: the average gap per customer (wrap this in a CTE and AVG), or customers who ever went more than 30 days between orders.

-- PostgreSQL: subtracting two dates gives days
SELECT customer_id, order_id, order_date,
       order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_last
FROM orders
ORDER BY customer_id, order_date;

-- MySQL
SELECT customer_id, order_id, order_date,
       DATEDIFF(order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)) AS days_since_last
FROM orders
ORDER BY customer_id, order_date;

36. Monthly cohort retention: who came back the next month?Hard

A cohort is a group of customers who started in the same period. Find each customer’s first-order month, then count how many of each cohort ordered again the following month. The LEFT JOIN keeps cohorts where nobody came back, so they show 0 rather than vanishing. For a full retention table, compute the months since each customer’s first order and pivot it (question 25).

-- PostgreSQL
WITH first_order AS (
  SELECT customer_id,
         DATE_TRUNC('month', MIN(order_date))::date AS cohort_month
  FROM orders
  GROUP BY customer_id
)
SELECT f.cohort_month,
       COUNT(DISTINCT f.customer_id) AS customers,
       COUNT(DISTINCT o.customer_id) AS ordered_next_month,
       ROUND(100.0 * COUNT(DISTINCT o.customer_id)
             / COUNT(DISTINCT f.customer_id), 1) AS retention_pct
FROM first_order f
LEFT JOIN orders o
       ON o.customer_id = f.customer_id
      AND o.order_date >= f.cohort_month + INTERVAL '1 month'
      AND o.order_date <  f.cohort_month + INTERVAL '2 months'
GROUP BY f.cohort_month
ORDER BY f.cohort_month;
-- MySQL: CAST(DATE_FORMAT(MIN(order_date), '%Y-%m-01') AS DATE) for the month,
--        and + INTERVAL 1 MONTH / + INTERVAL 2 MONTH
-- September cohort: 2 customers, both back in October: 100.0

37. Customers who ordered on 3 or more days in a rowHard

The “gaps and islands” trick. Number each customer’s order days in date order; the date minus that number stays the same across consecutive days, so it labels each streak. Group by it and count.

WITH days AS (
  SELECT DISTINCT customer_id, order_date
  FROM orders
),
numbered AS (
  SELECT customer_id, order_date,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
  FROM days
),
streaks AS (
  SELECT customer_id, order_date,
         order_date - CAST(rn AS INTEGER) AS streak_key   -- PostgreSQL
         -- MySQL: DATE_SUB(order_date, INTERVAL rn DAY) AS streak_key
  FROM numbered
)
SELECT customer_id,
       MIN(order_date) AS streak_start,
       MAX(order_date) AS streak_end,
       COUNT(*)        AS days_in_a_row
FROM streaks
GROUP BY customer_id, streak_key
HAVING COUNT(*) >= 3;
-- Customer 2: 4 to 6 October 2026

38. The median order valueHard

The median isn’t pulled up by a few huge orders the way the average is, which is why interviewers ask for it. PostgreSQL has PERCENTILE_CONT; MySQL has no median function, so rank the rows and average the middle one or two.13

-- PostgreSQL
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM orders
WHERE status = 'delivered';

-- MySQL
WITH ranked AS (
  SELECT amount,
         ROW_NUMBER() OVER (ORDER BY amount) AS rn,
         COUNT(*) OVER ()                    AS n
  FROM orders
  WHERE status = 'delivered'
)
SELECT AVG(amount) AS median_amount
FROM ranked
WHERE rn IN (FLOOR((n + 1) / 2), CEIL((n + 1) / 2));
-- 9 delivered orders, so the 5th smallest: 1899

39. What is an index, and when doesn’t it help?Hard

An index is a sorted lookup structure (usually a B-tree) on one or more columns that lets the database find rows without reading the whole table. It speeds up reads and slows writes, since every insert must update it. It doesn’t help when the column is wrapped in a function (YEAR(order_date) = 2026), when a LIKE pattern starts with a wildcard ('%sharma'), when the filter matches most of the table, or when a composite index’s first column isn’t in the filter. Both databases can index an expression if you really need one.

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

-- Uses it: filters on the leading column
SELECT * FROM orders WHERE customer_id = 2 AND order_date >= '2026-10-01';

-- Can't use it well: skips the leading column
SELECT * FROM orders WHERE order_date >= '2026-10-01';

-- Hides order_date inside a function (MySQL) …
SELECT * FROM orders WHERE YEAR(order_date) = 2026;
-- … so write a range instead
SELECT * FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

40. A query is slow. How do you speed it up?Hard

Measure before you change anything. EXPLAIN shows the plan; EXPLAIN ANALYZE runs the query and shows actual rows and timings, in recent MySQL and in PostgreSQL. Because it really runs the statement, never point it at an UPDATE or DELETE you don’t mean.815

EXPLAIN ANALYZE
SELECT c.city, SUM(o.amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-10-01'
GROUP BY c.city;

Then work down a short list:

• Look for full scans of big tables, and for estimated rows far from the actual rows.
• Index the columns you filter and join on, here orders.order_date and orders.customer_id.
• Keep filters index-friendly: ranges, not functions on columns (question 39).
• Filter and aggregate early, in a CTE, before joining big tables.
• Select only the columns you need instead of SELECT *.
• Remove any DISTINCT added to hide a join bug, and fix the join (question 20).