SQL Queries Interview Questions and Answers

Answer

This is the most asked SQL query question in India and it is really a question about ties and about empty results. The LIMIT 1 OFFSET 1 answer is wrong the moment two employees both earn 25,00,000, because the second row is still the same salary, so you return the highest salary again. The correct reading of 'second highest salary' is the second highest DISTINCT value.

Three approaches work. The subquery version selects MAX(salary) from rows where salary is less than the overall MAX, which handles ties naturally because MAX collapses duplicates. The DENSE_RANK version ranks distinct salary values and filters on rank 2, and DENSE_RANK is the right window function here because RANK would skip rank 2 entirely when two people tie at rank 1.

The LIMIT version works only if you add DISTINCT to the select list. The second half of the question is the empty case: if the company has one employee, or everyone earns the same, the correct output is a single row containing NULL, not zero rows. The MAX subquery returns NULL automatically, which is why interviewers prefer it.

The LIMIT and window versions return zero rows, so you have to wrap them in an outer SELECT to force a NULL row. Say all of this out loud, because the follow up is always 'now make it Nth highest' and the DENSE_RANK version generalises with a single number change while the correlated subquery version does not.

/* Approach 1: MAX of everything below the MAX. Returns NULL if no second value. */
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

/* Approach 2: DENSE_RANK, generalises to Nth immediately */
SELECT salary AS second_highest
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM (SELECT DISTINCT salary FROM employees) d
) r
WHERE rnk = 2;

/* Approach 3: LIMIT, only correct with DISTINCT, and returns 0 rows not NULL */
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Answer

Parameterise the rank rather than nesting more subqueries. The DENSE_RANK version needs one number changed and reads the same for N equal to 2 or 15. The classic pre-window-function answer used a correlated subquery counting how many distinct salaries are greater than the current row, which is elegant on a whiteboard and catastrophic on real data, because for each of the N rows it scans the table again, giving O(n squared) behaviour.

On an employees table of a few hundred rows nobody notices. On a payments table of forty million rows it never finishes, and that is exactly the follow up an interviewer at Walmart Global Tech or Flipkart will ask. Two details decide whether your answer is correct.

First, DISTINCT matters again: if three people earn 18,00,000 and you want the third highest salary, most business definitions mean the third distinct salary band, not the third row. DENSE_RANK over the raw rows gives you that automatically because tied rows share a rank. Second, you cannot filter on a window function in the WHERE clause, because window functions are evaluated after WHERE and GROUP BY in the logical processing order, so it has to sit in a subquery or a CTE.

Candidates who try WHERE DENSE_RANK() OVER (...) = 3 get a syntax error and often panic. Knowing the logical order of evaluation, FROM, WHERE, GROUP BY, HAVING, window functions, SELECT, DISTINCT, ORDER BY, LIMIT, is what lets you explain that error instead of guessing.

/* Nth highest salary, N is the only thing you change */
WITH ranked AS (
  SELECT emp_id, name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT emp_id, name, salary
FROM ranked
WHERE rnk = 3;

/* The old correlated version. Correct, but O(n^2) on large tables. */
SELECT DISTINCT e.salary
FROM employees e
WHERE 3 = (
  SELECT COUNT(DISTINCT e2.salary)
  FROM employees e2
  WHERE e2.salary >= e.salary
);

/* This is a SYNTAX ERROR, window functions cannot be used in WHERE */
/* SELECT name FROM employees WHERE DENSE_RANK() OVER (ORDER BY salary DESC) = 3; */

Answer

The trap is comparing against the company-wide average instead of the department average. You need a per-department average, and there are two clean ways to get it. The correlated subquery recomputes AVG(salary) for the current row's dept_id, which reads naturally and is what most candidates write.

The better answer in 2026 is a window function: AVG(salary) OVER (PARTITION BY dept_id) computes the department average alongside every row in a single pass, with no repeated scanning, and it also gives you the average in the output so the reviewer can sanity check the result. Since the filter is on a window function, it goes in an outer query or a CTE. Three edge cases separate a good answer.

Employees with a NULL dept_id are excluded by both versions, so ask whether the business wants them treated as their own group. NULL salaries are skipped by AVG, which is usually correct but means an employee with a NULL salary never appears in the output at all. And 'more than' is a strict inequality, so someone earning exactly the department average is excluded, which is worth confirming out loud because interviewers sometimes intend 'at or above'. If you want to show extra range, mention that the GROUP BY plus JOIN version, aggregating departments first into a derived table and joining back, is what an optimiser often produces anyway and is the easiest version to reuse when you also need the department headcount.

/* Window function version, single pass, shows the comparison value */
SELECT emp_id, name, dept_id, salary, dept_avg
FROM (
  SELECT e.*,
         AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
  FROM employees e
) t
WHERE salary > dept_avg;

/* Correlated subquery version */
SELECT e.emp_id, e.name, e.dept_id, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.dept_id = e.dept_id
);

/* Aggregate then join, easiest to extend with headcount */
SELECT e.emp_id, e.name, e.salary, d.dept_avg, d.headcount
FROM employees e
JOIN (
  SELECT dept_id, AVG(salary) AS dept_avg, COUNT(*) AS headcount
  FROM employees
  GROUP BY dept_id
) d ON d.dept_id = e.dept_id
WHERE e.salary > d.dept_avg;

Answer

This is the standard self join question and it tests whether you can hold two aliases of the same table in your head. Join employees to itself, aliasing one side as the worker and the other as the manager, matching worker.manager_id to manager.emp_id, then filter where the worker salary exceeds the manager salary. Use an INNER JOIN here, because an employee with no manager, typically the CEO with a NULL manager_id, has nothing to compare against and should not appear.

If you write a LEFT JOIN, the CEO row survives the join with NULL manager columns, and then the comparison salary greater than NULL evaluates to NULL, which is not TRUE, so the row is filtered out anyway. That is a good thing to say out loud because it shows you understand three valued logic rather than getting lucky. The naming discipline matters more than it looks: interviewers watch whether you alias as e and m or as a and b, because on a live screen with six columns the readable version is the one you can debug. The natural follow ups are 'now also show me by how much', which is a subtraction in the select list, and 'now show employees who earn more than their manager's manager', which is a second self join up the chain and leads directly into the recursive CTE question about the full reporting hierarchy.

SELECT w.emp_id,
       w.name AS employee,
       w.salary AS employee_salary,
       m.name AS manager,
       m.salary AS manager_salary,
       w.salary - m.salary AS excess
FROM employees w
JOIN employees m ON w.manager_id = m.emp_id
WHERE w.salary > m.salary
ORDER BY excess DESC;

/* Two levels up, employee vs their manager's manager */
SELECT w.name AS employee, g.name AS skip_manager
FROM employees w
JOIN employees m ON w.manager_id = m.emp_id
JOIN employees g ON m.manager_id = g.emp_id
WHERE w.salary > g.salary;

Answer

There are three ways to write an anti join and one of them is a live production bug waiting to happen. The LEFT JOIN with an IS NULL check on the right side is the most explicit: join customers to orders, keep the unmatched rows, and filter where orders.order_id IS NULL. NOT EXISTS with a correlated subquery is equally correct and often the fastest on PostgreSQL and MySQL 8, because the planner can stop scanning as soon as it finds one matching order.

NOT IN is the dangerous one. If the subquery SELECT customer_id FROM orders returns even a single NULL, the entire NOT IN comparison evaluates to NULL for every row rather than TRUE, and your query returns zero rows with no error, no warning and no hint. The reason is three valued logic: x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL, and that last term is UNKNOWN, so the whole AND chain can never be TRUE.

Orders with a NULL customer_id are exactly the kind of thing a guest checkout flow or a botched migration produces, so this is not theoretical. If you must use NOT IN, add WHERE customer_id IS NOT NULL to the subquery. Interviewers at Flipkart and Meesho ask this specific question because the failure is silent: the report just shows nothing and nobody notices for a week.

/* Safest and most readable */
SELECT c.customer_id, c.name, c.city
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

/* NOT EXISTS, NULL safe and usually the best plan */
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

/* NOT IN, returns ZERO rows if any orders.customer_id is NULL */
SELECT c.customer_id, c.name
FROM customers c
WHERE c.customer_id NOT IN (
  SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

Answer

WHERE filters individual rows before grouping. HAVING filters groups after aggregation. The clean way to demonstrate it is a query that needs both filters at once: consider only delivered orders placed in the current financial year, that is a row level filter and belongs in WHERE, then keep only the customers whose total across those orders exceeds 50,000, which is a group level filter and belongs in HAVING because SUM does not exist until grouping has happened.

Putting the row filter in HAVING usually still returns the right answer but is slower, because you aggregated rows you were going to discard. Putting the aggregate in WHERE is a hard error, MySQL says 'Invalid use of group function' and PostgreSQL says 'aggregate functions are not allowed in WHERE'. The underlying reason is the logical processing order: FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY.

That order also explains why you cannot reference a SELECT alias in WHERE, and why MySQL and PostgreSQL both let you reference a SELECT alias in ORDER BY, since ORDER BY runs last. One dialect difference worth naming: MySQL permits an alias in HAVING, PostgreSQL does not, so if you are unsure, repeat the expression rather than the alias and your query works on both. Interviewers use this question as a warm up and then immediately push into 'now add a filter on the count of distinct products', which is still HAVING.

SELECT o.customer_id,
       COUNT(*) AS delivered_orders,
       SUM(o.total_amount) AS lifetime_value
FROM orders o
WHERE o.status = 'DELIVERED'
  AND o.order_date >= '2026-04-01'   /* Indian FY starts 1 April */
  AND o.order_date <  '2027-04-01'
GROUP BY o.customer_id
HAVING SUM(o.total_amount) > 50000
   AND COUNT(*) >= 3
ORDER BY lifetime_value DESC;

/* This fails: aggregate in WHERE */
/* SELECT customer_id FROM orders WHERE SUM(total_amount) > 50000 GROUP BY customer_id; */

Answer

COUNT(*) counts rows, full stop, including rows where every column is NULL. COUNT(column) counts rows where that column is NOT NULL, so it is really a non-null counter wearing a row counter costume. COUNT(DISTINCT column) counts distinct non-null values, so it collapses duplicates and still ignores NULLs.

On the payments table this matters immediately: COUNT(*) tells you how many payment attempts exist, COUNT(paid_at) tells you how many of them actually completed if paid_at is only set on success, and COUNT(DISTINCT order_id) tells you how many orders were involved, which is smaller than the attempt count when a customer retried a failed UPI collect three times. Writing all three in one query and comparing the numbers is the fastest way to explain it in an interview and also the fastest way to find a data quality problem in real work. Two performance notes that senior interviewers appreciate: COUNT(*) on InnoDB is not free, MySQL has no stored row count for InnoDB so it walks an index, usually the smallest secondary index available, which is why COUNT(*) on a hundred million row table is slow even though it looks trivial. And COUNT(DISTINCT ...) generally requires either a sort or a hash table over the values, so it is materially more expensive than a plain COUNT, and on very large tables teams often accept an approximate count instead, using approx_count_distinct in Spark or HyperLogLog based functions in PostgreSQL extensions.

SELECT COUNT(*)                    AS attempts,
       COUNT(paid_at)              AS completed,     /* NULL paid_at excluded */
       COUNT(DISTINCT order_id)    AS orders_touched,
       COUNT(DISTINCT method)      AS methods_used
FROM payments
WHERE paid_at >= '2026-04-01' OR paid_at IS NULL;

/* Sample output
   attempts | completed | orders_touched | methods_used
      12480 |     11902 |          11315 |            4
   The gap between attempts and orders_touched is UPI retries.
*/

Answer

This is the question that catches people who reach for COUNT(*) by reflex. Start from departments and LEFT JOIN employees so that empty departments survive. Then the critical part: use COUNT(e.emp_id), not COUNT(*).

For an empty department, the LEFT JOIN produces one row with all employee columns NULL, so COUNT(*) counts that phantom row and reports 1 employee where the answer is 0. COUNT on a column from the outer joined table counts only non-null values, so it correctly reports 0. This single character difference is the whole point of the question and it is asked at Accenture, Cognizant and Meesho alike.

The second half is grouping: GROUP BY d.dept_id is enough in PostgreSQL if dept_id is the primary key, because PostgreSQL implements functional dependency detection and lets you select d.dept_name without grouping by it. MySQL 8 behaves the same way when ONLY_FULL_GROUP_BY is enabled, which it is by default. If you are unsure of the target dialect, group by both columns and the query is portable.

A useful extension to offer unprompted: add AVG(e.salary) and note that empty departments return NULL rather than 0, then wrap it in COALESCE if the report needs a zero. That shows you think about what the consumer of the query sees, not just whether it runs.

SELECT d.dept_id,
       d.dept_name,
       COUNT(e.emp_id)                    AS headcount,
       COALESCE(ROUND(AVG(e.salary), 0), 0) AS avg_salary
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY headcount DESC;

/* WRONG: COUNT(*) reports 1 for an empty department */
/* SELECT d.dept_name, COUNT(*) FROM departments d
   LEFT JOIN employees e ON e.dept_id = d.dept_id GROUP BY d.dept_name; */

Answer

Definitions are cheap, so demonstrate with rows. Take a customers table with three rows and an orders table where one customer has two orders, one has none, and one order points at a customer that no longer exists. INNER JOIN returns only matching pairs, so the customer with no orders and the orphan order both vanish, and the customer with two orders appears twice, which is the row multiplication that surprises people when their COUNT suddenly doubles.

LEFT JOIN keeps every customer, padding the order columns with NULL for the one with no orders, and still drops the orphan order. RIGHT JOIN is the mirror and is rarely used in practice because reordering the tables and using LEFT reads better. FULL OUTER JOIN keeps everything from both sides, so you see both the orderless customer and the orphan order with NULLs on the missing side, and note that MySQL has no FULL OUTER JOIN at all, you emulate it with a LEFT JOIN UNION a RIGHT JOIN.

CROSS JOIN pairs every row with every row, three customers times four orders gives twelve rows, and it is genuinely useful for generating a date spine or a complete category grid to left join against, not just a mistake. The key insight to state: a join can both lose rows and create rows, and the row count after a join is not a bug report, it is a modelling fact you should predict before you run it.

/* customers: 1 Aarav, 2 Priya, 3 Rohan
   orders:    101 -> 1, 102 -> 1, 103 -> 2, 104 -> 9 (orphan)

   INNER JOIN  -> 3 rows: (1,101) (1,102) (2,103)
   LEFT JOIN   -> 4 rows: adds (3, NULL)
   RIGHT JOIN  -> 4 rows: adds (NULL, 104)
   FULL OUTER  -> 5 rows: adds both
   CROSS JOIN  -> 12 rows: 3 x 4
*/

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;

/* MySQL has no FULL OUTER JOIN, emulate it */
SELECT c.name, o.order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id
UNION
SELECT c.name, o.order_id FROM customers c RIGHT JOIN orders o ON o.customer_id = c.customer_id;

Answer

Revenue lives at the line item level, not the order level, so you cannot answer this from orders.total_amount. Join order_items to products to get the category, and compute revenue as SUM(quantity * unit_price) rather than SUM(products.price), because unit_price on the line item is the price actually charged at the time of sale, after discounts and after any price change since. Using the current products.price silently rewrites history, and on a festival sale dataset it inflates revenue by whatever the discount was.

That distinction is the real content of this question. Then join orders so you can restrict to completed statuses, because cancelled and returned orders should not count as revenue, and the interviewer will ask what happens to returns if you do not raise it first. Round the output to two decimals for a report, and use a plain SUM for the amount rather than AVG.

If the schema has any chance of a line item with a NULL quantity, wrap it in COALESCE, since NULL times anything is NULL and SUM would skip that row entirely rather than error, meaning your revenue quietly undercounts. The natural follow ups from here are revenue per category per month, which is a GROUP BY with a date truncation, and the top three products within each category, which needs a window function.

SELECT p.category,
       COUNT(DISTINCT o.order_id)              AS orders,
       SUM(oi.quantity)                        AS units,
       ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
JOIN orders   o ON o.order_id  = oi.order_id
WHERE o.status IN ('DELIVERED', 'SHIPPED')
GROUP BY p.category
ORDER BY revenue DESC;

/* WRONG: uses today's catalogue price, not the price charged */
/* SUM(oi.quantity * p.price) AS revenue */

Answer

Group by the columns that define duplication and keep only groups with more than one row. GROUP BY name, city with HAVING COUNT(*) > 1 is the whole answer, and the important part is that the definition of duplicate is a business decision you should state, not assume. Two customers named Rahul Sharma in Mumbai may be genuinely different people, whereas two rows with the same email or the same phone number almost certainly are not, so in a real deduplication task you would key on a contact identifier rather than a name.

Add MIN(customer_id) and MAX(customer_id) to the output so the reviewer can see which rows to inspect, and add a string aggregation of the ids if the dialect supports it, GROUP_CONCAT in MySQL or STRING_AGG in PostgreSQL, so a single result row tells you every id in the duplicate cluster. Two refinements worth mentioning. Case and whitespace usually differ in real data, so grouping on LOWER(TRIM(name)) catches duplicates that a raw grouping misses, at the cost of not using an index on name. And if you need every duplicated ROW rather than a summary, switch to a window function: COUNT(*) OVER (PARTITION BY name, city) lets you keep all the original columns while filtering to rows in a group larger than one, which is what you actually want before deleting anything.

/* Summary of duplicate groups */
SELECT LOWER(TRIM(name)) AS norm_name,
       LOWER(TRIM(city)) AS norm_city,
       COUNT(*)          AS copies,
       MIN(customer_id)  AS keep_id,
       MAX(customer_id)  AS newest_id
FROM customers
GROUP BY LOWER(TRIM(name)), LOWER(TRIM(city))
HAVING COUNT(*) > 1
ORDER BY copies DESC;

/* Every duplicate ROW with all columns intact */
SELECT *
FROM (
  SELECT c.*,
         COUNT(*) OVER (PARTITION BY LOWER(TRIM(name)), LOWER(TRIM(city))) AS copies
  FROM customers c
) t
WHERE copies > 1
ORDER BY norm_name;

Answer

If order_date is a DATE, WHERE order_date >= CURRENT_DATE - INTERVAL 7 DAY is fine. If it is a TIMESTAMP, which it usually is, three separate traps appear. First, a BETWEEN comparison against a date literal like BETWEEN '2026-08-10' AND '2026-08-17' silently means midnight to midnight, so every order placed after 00:00:00 on the 17th is excluded and your report is short by up to a full day.

The fix is a half open range: greater than or equal to the start, strictly less than the day after the end. Second, applying a function to the column, such as WHERE DATE(order_date) >= ..., makes the expression non-sargable, so MySQL and PostgreSQL cannot use an index on order_date and you get a full table scan. Keep the column bare on the left side and put all the arithmetic on the right.

Third, and the one that costs Indian teams real money, timestamps are almost always stored in UTC while the business means IST. 'Last 7 days' to a category manager means seven IST days, and IST is UTC plus five and a half hours, so a naive UTC boundary shifts every daily bucket by five and a half hours and moves late evening orders into the wrong day. Convert the boundary, not the column: compute the IST midnight you want, translate it to UTC, and compare against the raw column so the index still works. Dialect note: MySQL uses INTERVAL 7 DAY and CONVERT_TZ, PostgreSQL uses INTERVAL '7 days' and AT TIME ZONE.

/* MySQL, index friendly, half open range */
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL 7 DAY
  AND order_date <  CURRENT_DATE + INTERVAL 1 DAY;

/* PostgreSQL equivalent */
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '7 days'
  AND order_date <  CURRENT_DATE + INTERVAL '1 day';

/* Column stored UTC, business wants IST days (PostgreSQL) */
WHERE order_date >= (CURRENT_DATE - INTERVAL '7 days')::timestamp AT TIME ZONE 'Asia/Kolkata'
  AND order_date <  (CURRENT_DATE + INTERVAL '1 day')::timestamp  AT TIME ZONE 'Asia/Kolkata';

/* NON SARGABLE, kills the index on order_date */
/* WHERE DATE(order_date) >= CURRENT_DATE - INTERVAL 7 DAY */

Answer

Aggregate first, then classify, then count. The most common mistake is trying to do it in one level, writing a CASE over SUM inside the same SELECT you then want to group by, which fails because you cannot group by an aggregate result in the same query block. Compute lifetime spend per customer in a CTE, apply the CASE in the next level, and count in the outer level.

Two correctness points about CASE itself. It is evaluated top to bottom and returns on the first matching branch, so ordering your thresholds descending means you can write simple greater than conditions without repeating the upper bound in every branch, and getting the order wrong puts everyone in the first bucket. Always include an ELSE branch, because a CASE with no matching WHEN returns NULL rather than erroring, and a NULL tier in a report is the kind of thing that reaches a leadership dashboard before anyone notices.

Also decide explicitly what happens to customers with zero orders: if you build the tiers from a LEFT JOIN, their spend is NULL, and NULL fails every comparison, so they land in the ELSE branch. That is usually the right outcome but say it deliberately. A nice extension to offer: the same tiering can be done with NTILE(3) OVER (ORDER BY spend) if the business wants equal sized thirds rather than fixed rupee thresholds, and knowing when to use which is the real judgement being tested.

WITH spend AS (
  SELECT c.customer_id,
         c.name,
         COALESCE(SUM(o.total_amount), 0) AS lifetime_spend
  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
),
tiered AS (
  SELECT customer_id, name, lifetime_spend,
         CASE
           WHEN lifetime_spend >= 100000 THEN 'Gold'
           WHEN lifetime_spend >=  25000 THEN 'Silver'
           WHEN lifetime_spend >      0  THEN 'Bronze'
           ELSE 'Inactive'
         END AS tier
  FROM spend
)
SELECT tier, COUNT(*) AS customers, ROUND(AVG(lifetime_spend), 0) AS avg_spend
FROM tiered
GROUP BY tier
ORDER BY avg_spend DESC;

Answer

UNION removes duplicate rows across the combined result. UNION ALL keeps everything. The deduplication is not free: the database has to sort or hash the entire combined set to find duplicates, which on large result sets is often the single most expensive operator in the plan.

The default should therefore be UNION ALL, and you should only reach for UNION when you genuinely need distinct rows and cannot get them more cheaply. The query where the choice changes the answer is any union of overlapping sets. Suppose you build a list of high value customers by combining those who spent over a lakh with those who placed more than twenty orders.

Many customers satisfy both. UNION ALL lists them twice, so a COUNT over the result overstates your high value cohort, while UNION reports each customer once. Conversely, when you union monthly partitions of a transactions table, the rows are naturally disjoint, so UNION does an expensive dedup that can never remove anything, and UNION ALL is strictly correct and faster.

Three mechanics worth naming: both branches must have the same number of columns with compatible types, the column names come from the first branch, and ORDER BY applies to the whole result so it goes at the very end, not inside a branch. If you need to order within a branch you wrap that branch in a subquery. Finally, UNION treats NULLs as equal for deduplication purposes, unlike a normal equality comparison, which surprises people.

/* Overlapping sets: UNION ALL double counts */
SELECT customer_id FROM orders GROUP BY customer_id HAVING SUM(total_amount) > 100000
UNION ALL
SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) > 20;
/* A customer meeting both conditions appears TWICE */

/* Correct for a cohort count */
SELECT COUNT(*) AS high_value_customers FROM (
  SELECT customer_id FROM orders GROUP BY customer_id HAVING SUM(total_amount) > 100000
  UNION
  SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) > 20
) hv;

/* Disjoint partitions: UNION ALL is correct AND faster */
SELECT * FROM payments_2025 UNION ALL SELECT * FROM payments_2026
ORDER BY paid_at DESC;   /* ORDER BY goes at the very end */

Answer

LEFT JOIN payments onto orders so unpaid orders survive, then use COALESCE to turn the NULL method into a readable label such as 'PENDING'. This is a straightforward query whose real content is the follow up: what happens when an order has more than one payment row, which is normal when the first UPI collect failed and the customer retried on card. The LEFT JOIN then returns two rows for that order and your order count is wrong.

There are two correct fixes depending on the question. If you want the latest payment attempt per order, use a window function to number the payments per order by paid_at descending and keep row number 1, or in PostgreSQL use DISTINCT ON (order_id) which is the shortest correct form. If you want the successful payment, filter the join to status = 'SUCCESS' and put that condition in the ON clause, not the WHERE clause, because a condition on the right table in WHERE converts the LEFT JOIN back into an INNER JOIN and your unpaid orders disappear again.

That ON versus WHERE distinction is the single most common LEFT JOIN bug in production reporting and it is worth stating explicitly even when the interviewer did not ask. The output should also show the amount so a reviewer can spot part payments, and COALESCE the amount to 0 for the pending rows.

/* Latest payment attempt per order, unpaid orders retained */
WITH latest AS (
  SELECT p.*,
         ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY paid_at DESC) AS rn
  FROM payments p
)
SELECT o.order_id,
       o.total_amount,
       COALESCE(l.method, 'PENDING') AS method,
       COALESCE(l.amount, 0)         AS paid
FROM orders o
LEFT JOIN latest l ON l.order_id = o.order_id AND l.rn = 1
ORDER BY o.order_date DESC;

/* Successful payment only: the filter MUST be in ON, not WHERE */
SELECT o.order_id, COALESCE(p.method, 'PENDING') AS method
FROM orders o
LEFT JOIN payments p
  ON p.order_id = o.order_id
 AND p.status = 'SUCCESS';   /* in WHERE this becomes an INNER JOIN */

Answer

GROUP BY city, ORDER BY the count descending, LIMIT 5. The misleading part is ties at the boundary: if the fifth and sixth cities both have 412 customers, LIMIT 5 picks one of them arbitrarily and the choice can change between runs because the sort is not stable on equal keys. If the business meaning is 'the top five cities', that is fine.

If it is 'every city in the top five by count', you need a window function with RANK or DENSE_RANK and a filter of rank at most 5, which returns six rows when there is a tie, and that is the correct behaviour. Making this distinction unprompted is what separates a candidate who has written reports from one who has memorised syntax. Two data quality points belong in the answer too.

City values in Indian datasets are notoriously inconsistent, with Bangalore and Bengaluru, Mumbai and Bombay, and case and whitespace variants all present, so a raw GROUP BY city produces a split count that under-reports the real leader; normalising with LOWER and TRIM, or better a mapping table, is the correct real world answer. And NULL cities form their own group in both MySQL and PostgreSQL, appearing as a single NULL row, which you should either exclude or label. Add a deterministic tie breaker to the ORDER BY, such as city name ascending, so the output is at least reproducible across runs.

/* Simple top 5, deterministic tie break */
SELECT city, COUNT(*) AS customers
FROM customers
WHERE city IS NOT NULL
GROUP BY city
ORDER BY customers DESC, city ASC
LIMIT 5;

/* Every city in the top 5 ranks, returns 6+ rows on a tie */
WITH counts AS (
  SELECT LOWER(TRIM(city)) AS city, COUNT(*) AS customers
  FROM customers
  WHERE city IS NOT NULL
  GROUP BY LOWER(TRIM(city))
)
SELECT city, customers, DENSE_RANK() OVER (ORDER BY customers DESC) AS rnk
FROM counts
QUALIFY rnk <= 5;   /* Snowflake/BigQuery. In MySQL/Postgres wrap in a subquery: */

SELECT city, customers FROM (
  SELECT city, customers, DENSE_RANK() OVER (ORDER BY customers DESC) AS rnk FROM counts
) t WHERE rnk <= 5;

Answer

Both forms are correct anti joins. NOT EXISTS with a correlated subquery on order_items reads as the business question does, 'products for which no order item exists', and both PostgreSQL and MySQL 8 implement it as an anti join that can stop scanning as soon as one match is found, which makes it a good default. The LEFT JOIN with IS NULL is equally valid and is easier to extend when you later want to also show the last time each product WAS ordered, because the joined table is already in scope.

What you should not use is NOT IN, for the NULL reason covered elsewhere: a single NULL product_id in order_items makes the result empty with no error. For a catalogue cleanup query the interviewer usually wants more than the id list, so add the product name, category, price and how long it has been in the catalogue, because a product added yesterday with no orders is not the same problem as one added three years ago. A refinement that impresses: restrict the order_items side to a time window, so the question becomes 'products with no orders in the last 180 days', which is the actually useful business query and which changes NOT EXISTS into a correlated subquery with a join back to orders for the date. That version also demonstrates that you know order_items has no date column of its own and you have to reach through orders to get one.

/* Never ordered at all */
SELECT p.product_id, p.name, p.category, p.price
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id
);

/* LEFT JOIN form */
SELECT p.product_id, p.name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
WHERE oi.order_item_id IS NULL;

/* The useful version: no orders in the last 180 days */
SELECT p.product_id, p.name, p.category
FROM products p
WHERE NOT EXISTS (
  SELECT 1
  FROM order_items oi
  JOIN orders o ON o.order_id = oi.order_id
  WHERE oi.product_id = p.product_id
    AND o.order_date >= CURRENT_DATE - INTERVAL '180 days'
);

Answer

MIN and MAX over order_date grouped by customer gives you both endpoints in one pass, and the day gap is a date difference on those two aggregates. The dialect difference is the only real complexity: MySQL uses DATEDIFF(end, start) which returns whole days and takes the arguments in what feels like the reverse order, while PostgreSQL uses plain subtraction on dates returning an integer, or EXTRACT(EPOCH FROM ...) divided by 86400 when the columns are timestamps and you want fractional days. Getting DATEDIFF's argument order backwards produces negative numbers, which is a classic careless error that an interviewer will spot instantly in the sample output.

Three things to handle. Customers with exactly one order produce a gap of zero, which is correct but should be called out because a stakeholder reading 'average gap' will be misled if single order customers are included. Customers with no orders do not appear at all unless you LEFT JOIN from customers, in which case MIN and MAX return NULL and the gap is NULL.

And if order_date is a timestamp, MAX minus MIN in PostgreSQL gives an interval rather than an integer, so you need an explicit cast or EXTRACT. Adding COUNT(*) as order count in the same query is nearly free and makes the result immediately more useful, and it lets you filter to customers with at least two orders when the gap is the metric you actually care about.

/* MySQL */
SELECT c.customer_id, c.name,
       MIN(o.order_date) AS first_order,
       MAX(o.order_date) AS last_order,
       COUNT(*)          AS orders,
       DATEDIFF(MAX(o.order_date), MIN(o.order_date)) AS days_active
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
HAVING COUNT(*) >= 2
ORDER BY days_active DESC;

/* PostgreSQL, DATE columns */
       MAX(o.order_date)::date - MIN(o.order_date)::date AS days_active

/* PostgreSQL, TIMESTAMP columns, fractional days */
       EXTRACT(EPOCH FROM (MAX(o.order_date) - MIN(o.order_date))) / 86400 AS days_active

Answer

Aggregate functions skip NULLs, arithmetic operators propagate them. SUM(amount) ignores NULL rows entirely and returns the total of the non-null values, which is usually what you want, and it returns NULL rather than 0 when every row is NULL or when there are no rows at all, which is usually not what a dashboard wants. AVG(amount) divides by the count of non-null values, not by the row count, so if half your payments have a NULL amount the average is computed over the other half and is silently higher than a naive reader expects.

Any arithmetic involving NULL yields NULL, so amount * 1.18 for a GST calculation returns NULL, and amount + fee returns NULL if either is missing, which is why a total built by adding columns behaves differently from a total built by SUM. The practical fixes: wrap the column in COALESCE(amount, 0) when a missing value genuinely means zero, wrap the aggregate in COALESCE(SUM(amount), 0) when you need the empty result to display as zero, and use COUNT(amount) alongside COUNT(*) so the report exposes how many rows were skipped instead of hiding it. Also remember that NULL equality never works, so WHERE amount = NULL matches nothing and you must write IS NULL. In GROUP BY, all NULLs collapse into a single group, and in ORDER BY, MySQL sorts NULLs first ascending while PostgreSQL sorts them last, which you control with NULLS FIRST or NULLS LAST in PostgreSQL.

SELECT method,
       COUNT(*)                       AS rows_total,
       COUNT(amount)                  AS rows_with_amount,
       COALESCE(SUM(amount), 0)       AS revenue,
       ROUND(AVG(amount), 2)          AS avg_over_nonnull,
       ROUND(SUM(COALESCE(amount, 0)) / COUNT(*), 2) AS avg_over_all_rows
FROM payments
WHERE status = 'SUCCESS'
GROUP BY method
ORDER BY revenue DESC;

/* NULL behaviour cheat sheet
   SUM(col)        skips NULLs, returns NULL if all NULL or no rows
   AVG(col)        divides by COUNT(col), NOT COUNT(*)
   col * 1.18      NULL if col is NULL
   col = NULL      never TRUE, use IS NULL
   GROUP BY col    all NULLs form one group
*/

Answer

This is the canonical top N per group problem and the window function is the whole answer. Aggregate revenue per product first, then rank within each category with PARTITION BY p.category ORDER BY revenue DESC, then filter the rank in an outer query because window functions cannot appear in WHERE. Which ranking function you pick is a business decision, not a style choice, and the interviewer will ask you to justify it.

ROW_NUMBER always produces 1, 2, 3, 4 with no gaps and no duplicates, so on a tie it picks a winner arbitrarily and returns exactly three rows per category, which is right when you need a fixed size list for a UI carousel. RANK produces 1, 1, 3 on a two way tie, so it skips the next value and a filter of rank at most 3 returns two rows in that category, which surprises people. DENSE_RANK produces 1, 1, 2, so the same filter returns four rows including both tied products, which is right when the business says 'show the top three revenue levels'.

State the tie behaviour before the interviewer asks. Also add a deterministic secondary sort key such as product_id, because without it a re-run can return different rows for tied values and someone will file a bug about a flaky report.

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

/* Revenue 900, 900, 800, 700 gives
   ROW_NUMBER  1 2 3 4
   RANK        1 1 3 4
   DENSE_RANK  1 1 2 3
*/

Answer

Aggregate to one row per day first, then apply SUM(daily_revenue) OVER (ORDER BY order_day) to accumulate. The mistake candidates make is applying the window directly to raw order rows, which produces a running total that advances within a day in an arbitrary order and gives multiple different values for the same date. Aggregate then accumulate, always in that order.

The subtle part is the window frame. When you write an ORDER BY inside OVER without an explicit frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE means peer rows, that is rows with an equal ORDER BY value, are all included at once. Once you have aggregated to one row per day there are no peers, so the default behaves as you expect.

But if you skip the aggregation, RANGE lumps every row of the same day together and returns the day's end total on every row of that day, while ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW would accumulate row by row. Knowing that RANGE and ROWS differ exactly when there are ties in the ORDER BY is a strong senior signal. The Indian financial year boundary belongs in the WHERE clause, 1 April to 31 March, and if you also want days with zero revenue represented you need a date spine generated with a recursive CTE or PostgreSQL's generate_series, left joined to the daily totals, otherwise the running total simply skips empty days.

WITH daily AS (
  SELECT CAST(order_date AS DATE) AS order_day,
         SUM(total_amount)        AS revenue
  FROM orders
  WHERE status = 'DELIVERED'
    AND order_date >= '2026-04-01'   /* Indian FY 2026-27 */
    AND order_date <  '2027-04-01'
  GROUP BY CAST(order_date AS DATE)
)
SELECT order_day,
       revenue,
       SUM(revenue) OVER (ORDER BY order_day
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       AVG(revenue) OVER (ORDER BY order_day
                          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7day_avg
FROM daily
ORDER BY order_day;

/* Include zero revenue days (PostgreSQL) */
/* FROM generate_series('2026-04-01'::date, '2027-03-31'::date, '1 day') d(day)
   LEFT JOIN daily ON daily.order_day = d.day */

Answer

Roll revenue up to a month bucket, then use LAG(revenue) OVER (ORDER BY month) to pull the previous month onto the same row, and compute the percentage change. Before window functions this needed a self join on month minus one, which is both slower and wrong at year boundaries unless you are careful, so LAG is the answer the interviewer wants. Four details decide correctness.

First, the first month has no previous row, so LAG returns NULL and the growth is NULL, which is correct; do not COALESCE it to zero because zero growth and no prior data are different facts. Second, division by zero: if the previous month had zero revenue the percentage is undefined, and in PostgreSQL you get a division by zero error while MySQL returns NULL, so guard it with NULLIF(prev, 0). Third, missing months.

If March had no orders at all, there is no March row, so April's LAG reaches back to February and silently labels a two month change as month over month. A date spine left joined to the monthly totals fixes it, and mentioning this failure is what separates a real analyst answer. Fourth, month truncation differs by dialect: PostgreSQL uses DATE_TRUNC('month', order_date), MySQL uses DATE_FORMAT(order_date, '%Y-%m-01') or the newer functions, and grouping on a string like '2026-04' sorts correctly only because the format is zero padded, which is worth being deliberate about.

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month,
         SUM(total_amount)               AS revenue
  FROM orders
  WHERE status = 'DELIVERED'
  GROUP BY DATE_TRUNC('month', order_date)
)
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)
       , 2) AS mom_growth_pct
FROM monthly
ORDER BY month;

/* MySQL month bucket */
/* DATE_FORMAT(order_date, '%Y-%m-01') AS month */

/* Sample output
   month      | revenue  | prev_revenue | mom_growth_pct
   2026-04-01 | 1840000  | NULL         | NULL
   2026-05-01 | 2107000  | 1840000      |  14.51
   2026-06-01 | 1993000  | 2107000      |  -5.41
*/

Answer

This is the gaps and islands pattern and the trick is one line long once you see it. For each user, order the distinct login dates and number them with ROW_NUMBER. Subtract that row number from the date.

Within a run of consecutive dates the date advances by one and the row number advances by one, so the difference is constant, and that constant becomes a group key identifying the island. Dates on either side of a gap get a different constant. Group by user and that key, count the rows, and keep groups of size three or more, optionally reporting MIN and MAX of the date as the streak boundaries.

Two preparation steps matter. Deduplicate first: if the source is a raw event log with many logins per day, cast to date and take DISTINCT, otherwise the row numbering is meaningless. And be careful with the arithmetic across dialects.

In PostgreSQL, date minus integer gives a date, so the group key is a date. In MySQL you write DATE_SUB(login_date, INTERVAL rn DAY). In both cases the actual value of the key is meaningless, only its constancy matters.

Interviewers at Meesho, Swiggy and Walmart Global Tech ask this in the streak form, the consecutive months form, and the 'find continuous ranges of ids' form, all of which are the same query. Once you name the technique by name, the follow ups become mechanical.

WITH logins AS (
  SELECT DISTINCT user_id, CAST(login_at AS DATE) AS d
  FROM user_logins
),
numbered AS (
  SELECT user_id, d,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY d) AS rn
  FROM logins
),
islands AS (
  SELECT user_id, d, d - rn AS grp   /* PostgreSQL: date minus int */
  FROM numbered
)
SELECT user_id,
       MIN(d)   AS streak_start,
       MAX(d)   AS streak_end,
       COUNT(*) AS streak_length
FROM islands
GROUP BY user_id, grp
HAVING COUNT(*) >= 3
ORDER BY streak_length DESC;

/* MySQL group key */
/* DATE_SUB(d, INTERVAL rn DAY) AS grp */

/* Why it works
   d:  10 11 12   15 16
   rn:  1  2  3    4  5
   d-rn: 9  9  9   11 11   <- two islands
*/

Answer

The logic is identical in both engines, the syntax is not, and the difference is a real MySQL restriction rather than a style preference. In PostgreSQL you write a DELETE with a USING clause or a subquery selecting the ids to keep, and it just works. In MySQL you hit error 1093, 'You cannot specify target table for update in FROM clause', because MySQL will not let you read the table you are deleting from inside a subquery in the same statement.

The standard workaround is to wrap the subquery in another derived table, which forces MySQL to materialise it first, or to use MySQL's multi table DELETE with a self join, which is the version most production runbooks use because it is faster and clearer. The self join form deletes rows where a row with a smaller id and identical duplicate key exists. Before running any of this in an interview or in production, say the three safety steps: run the equivalent SELECT first and eyeball the count, take a backup or run inside a transaction so you can roll back, and consider adding a unique index afterwards so the duplicates cannot come back, since deleting duplicates without fixing the constraint means doing it again next quarter. If the table is large, deleting in batches with a LIMIT avoids holding a huge transaction and blowing up replication lag on read replicas.

/* Step 1 ALWAYS: see what you are about to delete */
SELECT c.*
FROM customers c
JOIN customers k
  ON k.name = c.name AND k.city = c.city AND k.customer_id < c.customer_id;

/* MySQL: multi table DELETE with a self join */
DELETE c
FROM customers c
JOIN customers k
  ON k.name = c.name
 AND k.city = c.city
 AND k.customer_id < c.customer_id;

/* MySQL alternative, derived table dodges error 1093 */
DELETE FROM customers
WHERE customer_id NOT IN (
  SELECT keep_id FROM (
    SELECT MIN(customer_id) AS keep_id FROM customers GROUP BY name, city
  ) tmp
);

/* PostgreSQL */
DELETE FROM customers c
USING customers k
WHERE k.name = c.name AND k.city = c.city AND k.customer_id < c.customer_id;

/* Step 3: stop it happening again */
CREATE UNIQUE INDEX uq_customer_name_city ON customers (name, city);

Answer

Neither MySQL nor PostgreSQL has a portable PIVOT keyword, so the technique everyone actually uses is conditional aggregation: one SUM with a CASE inside it per output column. SUM(CASE WHEN month = 4 THEN revenue ELSE 0 END) AS apr, repeated for each month. The CASE runs per row and contributes the value only when the row belongs to that bucket, so the aggregate collapses the rows into a single wide row per group.

Whether you use ELSE 0 or ELSE NULL changes the result for empty buckets: ELSE 0 gives a zero in the cell, ELSE NULL leaves it NULL and lets AVG behave correctly if you switch aggregates later. For a revenue report zero is usually right. Two things interviewers probe.

First, the column list is fixed at query write time, so a truly dynamic pivot needs the application to generate the SQL or a stored procedure to build it, and there is no pure SQL way around that; PostgreSQL's crosstab function in the tablefunc extension exists but still needs the output column list declared. Second, this pattern generalises far beyond months: counting orders by status as columns, or counting payments by method as columns, is the same query and is the shape most dashboards want. Ordering the months by Indian financial year, April through March, is a nice touch that shows domain awareness.

SELECT c.state,
       SUM(CASE WHEN EXTRACT(MONTH FROM o.order_date) = 4  THEN o.total_amount ELSE 0 END) AS apr,
       SUM(CASE WHEN EXTRACT(MONTH FROM o.order_date) = 5  THEN o.total_amount ELSE 0 END) AS may,
       SUM(CASE WHEN EXTRACT(MONTH FROM o.order_date) = 6  THEN o.total_amount ELSE 0 END) AS jun,
       SUM(o.total_amount) AS fy_total
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'DELIVERED'
  AND o.order_date >= '2026-04-01' AND o.order_date < '2027-04-01'
GROUP BY c.state
ORDER BY fy_total DESC;

/* Same pattern, orders by status as columns */
SELECT customer_id,
       COUNT(CASE WHEN status = 'DELIVERED' THEN 1 END) AS delivered,
       COUNT(CASE WHEN status = 'CANCELLED' THEN 1 END) AS cancelled,
       COUNT(CASE WHEN status = 'RETURNED'  THEN 1 END) AS returned
FROM orders
GROUP BY customer_id;

Answer

A recursive CTE has two parts joined by UNION ALL: an anchor member that selects the starting rows, and a recursive member that joins the CTE back to the base table to walk one level further. Here the anchor selects the manager themselves with depth 0, and the recursive member joins employees to the CTE on manager_id equals the CTE's emp_id, adding one to the depth. It terminates when the recursive member returns no rows, meaning no more direct reports.

Use UNION ALL rather than UNION unless you specifically need deduplication, because UNION forces a dedup pass on every iteration. Three practical points. MySQL requires the RECURSIVE keyword and enforces cte_max_recursion_depth, defaulting to 1000, which stops a runaway query but also silently limits legitimate deep hierarchies.

PostgreSQL requires RECURSIVE too but has no default depth cap, so a cycle in the data, an employee who is transitively their own manager after a bad data fix, loops forever and eats the connection. Guard against cycles by carrying a path array or string and checking that the next emp_id is not already in it, which PostgreSQL also supports natively with the CYCLE clause in version 14 and later. Finally, building the path as a concatenated string of names gives you a readable org chart in one column, which is what the interviewer usually wants to see next, and ordering by that path renders the tree in the right visual order.

WITH RECURSIVE chain AS (
  /* anchor: the manager we start from */
  SELECT emp_id, name, manager_id, 0 AS depth,
         CAST(name AS VARCHAR(1000)) AS path
  FROM employees
  WHERE emp_id = 101

  UNION ALL

  /* recursive: everyone reporting into a row already in chain */
  SELECT e.emp_id, e.name, e.manager_id, c.depth + 1,
         CAST(c.path || ' > ' || e.name AS VARCHAR(1000))
  FROM employees e
  JOIN chain c ON e.manager_id = c.emp_id
  WHERE c.depth < 20                 /* cycle guard */
)
SELECT emp_id, name, depth, path
FROM chain
ORDER BY path;

/* Sample output
   emp_id | name    | depth | path
      101 | Anita   |     0 | Anita
      118 | Rohan   |     1 | Anita > Rohan
      204 | Sneha   |     2 | Anita > Rohan > Sneha
*/

/* MySQL string concat uses CONCAT(c.path, ' > ', e.name) */

Answer

ROWS counts physical rows relative to the current row. RANGE works on the ORDER BY value, so it includes all peer rows, that is every row whose ORDER BY value equals the current row's. When your ORDER BY key is unique the two are identical, which is why most people never notice the distinction and then get burned.

Take daily revenue where two orders share the same date. With ORDER BY order_date and the default frame, which is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, both rows for that date receive the same running total, the total including both, because they are peers. With ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, the first row gets the total up to itself and the second gets the total including both, so the running total advances within the day in whatever order the rows happen to arrive.

Neither is wrong, they answer different questions, but silently getting RANGE when you meant ROWS is a common source of a report that does not tie out. The rule to state: if you write ORDER BY inside OVER and no frame clause, you get the RANGE default, so always write the frame explicitly when it matters. Moving averages must use ROWS, since ROWS BETWEEN 6 PRECEDING AND CURRENT ROW means the last seven rows while RANGE BETWEEN 6 PRECEDING AND CURRENT ROW is only legal on numeric or date types and means a value window, not a row count.

/* Two orders on 2026-04-10: 500 and 300 */
SELECT order_id, order_date, total_amount,
       SUM(total_amount) OVER (ORDER BY order_date
         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_total,
       SUM(total_amount) OVER (ORDER BY order_date
         ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_total
FROM orders
ORDER BY order_date, order_id;

/* Output
   order_id | order_date | amount | range_total | rows_total
        901 | 2026-04-09 |    200 |         200 |        200
        902 | 2026-04-10 |    500 |        1000 |        700
        903 | 2026-04-10 |    300 |        1000 |       1000
   RANGE gives both 2026-04-10 rows the same value.
*/

/* 7 day moving average MUST use ROWS */
AVG(revenue) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

Answer

The greatest-n-per-group problem. The portable answer is ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) with an outer filter on row number 1, which works identically in MySQL 8 and PostgreSQL and gives you every column of the winning row without a second lookup. The correlated subquery version, WHERE order_date = (SELECT MAX(order_date) FROM orders o2 WHERE o2.customer_id = o.customer_id), is readable but has two problems: it scans the orders table again per customer unless the optimiser rewrites it, and it returns two rows if a customer placed two orders at the exact same timestamp, which happens more often than you expect with bulk imports and retried API calls.

PostgreSQL offers DISTINCT ON (customer_id) with an ORDER BY customer_id, order_date DESC, which is the shortest correct form and is genuinely fast, but it is PostgreSQL only so say so rather than writing it blind. The GROUP BY MAX approach, selecting customer_id and MAX(order_date), is fine when you only need those two columns and wrong the moment you also want order_id or total_amount, because those columns are not functionally determined by the grouping and either error under ONLY_FULL_GROUP_BY or, worse, return an arbitrary row's value in a lax MySQL configuration. Naming that specific failure is a strong signal, because a query that silently returns the wrong order_id is far more dangerous than one that errors.

/* Portable: ROW_NUMBER, works on MySQL 8 and PostgreSQL */
WITH ranked AS (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY order_date DESC, order_id DESC) AS rn
  FROM orders o
)
SELECT customer_id, order_id, order_date, total_amount, status
FROM ranked
WHERE rn = 1;

/* PostgreSQL only, shortest correct form */
SELECT DISTINCT ON (customer_id)
       customer_id, order_id, order_date, total_amount
FROM orders
ORDER BY customer_id, order_date DESC, order_id DESC;

/* BROKEN: other columns are not determined by the grouping */
/* SELECT customer_id, order_id, MAX(order_date) FROM orders GROUP BY customer_id; */

Answer

The two engines diverge sharply here and knowing both is a genuine differentiator. MySQL supports UPDATE with a JOIN directly in the statement: UPDATE customers c JOIN (derived table) s ON ... SET c.tier = ....

PostgreSQL has no JOIN in UPDATE; instead it has UPDATE ... SET ... FROM ...

WHERE, where the FROM clause introduces the other relation and the WHERE clause supplies the join condition. Writing the MySQL form against PostgreSQL is a syntax error and vice versa, and interviewers who run a Postgres shop use this to check whether you have actually used the engine. Beyond syntax, three things matter.

Customers with no orders are not in the derived table at all, so with an inner join they keep their old tier rather than being reset; if the business wants them set to 'Inactive', use a LEFT JOIN in MySQL or a separate UPDATE for the unmatched rows in PostgreSQL. Ambiguity in PostgreSQL's FROM form is a real hazard: if the same customer_id appears twice in the source relation, the update uses one of the matching rows non-deterministically with no error, so always aggregate the source to one row per key first. And any bulk UPDATE on a large table should run in batches inside explicit transactions, because a single statement touching millions of rows holds locks, bloats the undo or WAL, and spikes replication lag on the read replicas that your dashboards read from.

/* MySQL */
UPDATE customers c
JOIN (
  SELECT customer_id, SUM(total_amount) AS spend
  FROM orders WHERE status = 'DELIVERED'
  GROUP BY customer_id
) s ON s.customer_id = c.customer_id
SET c.tier = CASE WHEN s.spend >= 100000 THEN 'Gold'
                  WHEN s.spend >=  25000 THEN 'Silver'
                  ELSE 'Bronze' END;

/* PostgreSQL: no JOIN in UPDATE, use FROM plus WHERE */
UPDATE customers c
SET tier = CASE WHEN s.spend >= 100000 THEN 'Gold'
                WHEN s.spend >=  25000 THEN 'Silver'
                ELSE 'Bronze' END
FROM (
  SELECT customer_id, SUM(total_amount) AS spend
  FROM orders WHERE status = 'DELIVERED'
  GROUP BY customer_id
) s
WHERE s.customer_id = c.customer_id;

/* Customers with zero orders keep their old tier in both, fix separately */
UPDATE customers SET tier = 'Inactive'
WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

Answer

MySQL uses INSERT ... ON DUPLICATE KEY UPDATE, which triggers when the insert would violate any unique or primary key. PostgreSQL uses INSERT ...

ON CONFLICT (columns) DO UPDATE SET, where you name the conflict target explicitly. Both require an actual unique constraint on the key columns; without one there is nothing to conflict on and every run inserts a new duplicate row, which is the number one reason a nightly summary job produces three rows for the same day after someone rebuilt the table and forgot the index. In MySQL you reference the would-be-inserted values with VALUES(col) in older versions, and MySQL 8.0.20 deprecated that in favour of a row alias, so the modern form is INSERT ...

AS new ... ON DUPLICATE KEY UPDATE revenue = new.revenue. In PostgreSQL the pseudo-table is called EXCLUDED, so you write revenue = EXCLUDED.revenue, and you can add a WHERE clause to the DO UPDATE so the update only applies conditionally, for example only when the new value is larger.

Two behaviours worth naming: MySQL's ON DUPLICATE KEY UPDATE consumes auto increment values even when it updates rather than inserts, so ids develop gaps, and PostgreSQL's ON CONFLICT DO NOTHING is the right choice for an idempotent insert where you genuinely do not care about the existing row. For a summary table, upsert is far safer than DELETE then INSERT, because the latter leaves a window where the row does not exist and a dashboard query can read a hole.

/* The constraint that makes upsert possible */
CREATE UNIQUE INDEX uq_daily_summary ON daily_revenue (summary_date);

/* MySQL 8.0.20+ row alias form (VALUES() is deprecated) */
INSERT INTO daily_revenue (summary_date, orders, revenue)
VALUES ('2026-08-16', 412, 1840000) AS new
ON DUPLICATE KEY UPDATE
  orders  = new.orders,
  revenue = new.revenue;

/* MySQL INSERT ... SELECT form, still uses VALUES() for the new row */
INSERT INTO daily_revenue (summary_date, orders, revenue)
SELECT CAST(order_date AS DATE), COUNT(*), SUM(total_amount)
FROM orders
WHERE status = 'DELIVERED' AND order_date >= CURRENT_DATE - INTERVAL 1 DAY
GROUP BY CAST(order_date AS DATE)
ON DUPLICATE KEY UPDATE
  orders  = VALUES(orders),
  revenue = VALUES(revenue);

/* PostgreSQL */
INSERT INTO daily_revenue (summary_date, orders, revenue)
SELECT order_date::date, COUNT(*), SUM(total_amount)
FROM orders
WHERE status = 'DELIVERED' AND order_date >= CURRENT_DATE - INTERVAL '1 day'
GROUP BY order_date::date
ON CONFLICT (summary_date) DO UPDATE
SET orders  = EXCLUDED.orders,
    revenue = EXCLUDED.revenue
WHERE daily_revenue.revenue IS DISTINCT FROM EXCLUDED.revenue;

Answer

There are two acceptable answers and the right one depends on the engine. PostgreSQL has the ordered set aggregate PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary), which interpolates between the two middle values for an even count and is the textbook median, and PERCENTILE_DISC(0.5) which returns an actual value from the data. MySQL has neither, so you write it with window functions: number the rows within each department ascending, count the rows, and average the values at positions floor((n+1)/2) and floor((n+2)/2), which selects the single middle row for an odd count and the two middle rows for an even count, averaging them.

That expression is worth memorising because it is the only median formula that handles both parities without an IF. The interviewer is checking three things: that you know median is not AVG and is robust to outliers, which is exactly why a compensation analysis uses median rather than mean; that you handle the even count case at all, since many candidates return only the lower middle value; and that you know the sorting has to be per department, meaning PARTITION BY dept_id in both the ROW_NUMBER and the COUNT. A follow up you should expect is p90 or p95, which in PostgreSQL is a one character change and in MySQL means switching to a rank based filter, and a further follow up is why p95 latency matters more than average latency in an SLA conversation.

/* PostgreSQL: built in */
SELECT dept_id,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary,
       PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary) AS p90_salary
FROM employees
GROUP BY dept_id;

/* MySQL 8: window functions, handles odd and even counts */
WITH ranked AS (
  SELECT dept_id, salary,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary) AS rn,
         COUNT(*)     OVER (PARTITION BY dept_id)                 AS n
  FROM employees
)
SELECT dept_id, AVG(salary) AS median_salary
FROM ranked
WHERE rn IN (FLOOR((n + 1) / 2), FLOOR((n + 2) / 2))
GROUP BY dept_id;

/* n = 5 picks rn 3 and 3, one row
   n = 6 picks rn 3 and 4, averaged */

Answer

Sargable means Search ARGument ABLE, that is, the predicate can be used to seek into an index rather than forcing a scan. An index on order_date stores the raw column values in sorted order. YEAR(order_date) is a different value that is not stored anywhere, so the engine has no way to seek and must compute the function for every row in the table, which is a full scan even though a perfectly good index exists.

The rewrite is a half open range on the bare column, greater than or equal to 1 January and strictly less than 1 January of the next year, which the optimiser turns into a single index range scan. The same rule applies to every wrapped column: UPPER(email) = 'X' cannot use an index on email, DATE(created_at) = CURRENT_DATE cannot use an index on created_at, and salary * 12 > 1200000 cannot use an index on salary but salary > 100000 can. Implicit conversion causes the same problem invisibly: comparing a VARCHAR phone column to a numeric literal makes MySQL convert the column, not the literal, and the index goes unused with no warning in the query text.

Two escapes exist when you genuinely need the function: a functional or expression index, supported by PostgreSQL for years and by MySQL 8, or a generated column indexed alongside. Prove any of this with EXPLAIN and look for type ALL and rows equal to the table size in MySQL, or Seq Scan in PostgreSQL.

/* NOT sargable: full scan even with an index on order_date */
SELECT * FROM orders WHERE YEAR(order_date) = 2026;

/* Sargable rewrite: index range scan */
SELECT * FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date <  '2027-01-01';

/* Other non sargable patterns and their fixes */
/* UPPER(email) = 'A@B.COM'   -> store lowercase, or create an expression index */
/* salary * 12 > 1200000      -> salary > 100000 */
/* phone = 9876543210         -> phone = '9876543210' (avoid implicit cast) */
/* name LIKE '%sharma'        -> leading wildcard, no index possible */

/* When you truly need the function, index the expression */
CREATE INDEX idx_orders_year ON orders ((EXTRACT(YEAR FROM order_date)));  /* PostgreSQL */
CREATE INDEX idx_email_lower ON customers ((LOWER(email)));               /* MySQL 8 */

Answer

Reduce to one row per customer per month first, because a customer with forty orders in April must contribute exactly one April row or the consecutiveness logic collapses. Then there are two clean techniques. The gaps and islands version numbers each customer's distinct months and subtracts the row number from a month index, giving a constant per run, then groups and keeps runs of length three or more.

The LAG version is more direct for exactly three: pull the previous and the month before that onto each row with LAG(month, 1) and LAG(month, 2), and keep rows where the current month is one after the previous and two after the one before that. Comparing months needs a numeric month index rather than dates, because subtracting date months across a year boundary is fiddly; the standard trick is year times 12 plus month, which makes December 2026 and January 2027 differ by exactly one. That single line is what most candidates get wrong, producing a query that works within a year and breaks every December.

State the business intent too: 'three consecutive months' usually means retention, so the output people actually want is the customer list plus the window in which the streak occurred, not just the ids. If the interviewer then asks for the longest streak per customer, that is the gaps and islands version with a MAX over the group sizes, so leading with that technique sets you up for the follow up.

WITH monthly AS (
  SELECT DISTINCT customer_id,
         EXTRACT(YEAR FROM order_date) * 12 + EXTRACT(MONTH FROM order_date) AS m
  FROM orders
  WHERE status = 'DELIVERED'
),
with_lags AS (
  SELECT customer_id, m,
         LAG(m, 1) OVER (PARTITION BY customer_id ORDER BY m) AS m1,
         LAG(m, 2) OVER (PARTITION BY customer_id ORDER BY m) AS m2
  FROM monthly
)
SELECT DISTINCT customer_id
FROM with_lags
WHERE m - m1 = 1 AND m - m2 = 2;

/* Longest streak per customer, gaps and islands */
WITH numbered AS (
  SELECT customer_id, m,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY m) AS rn
  FROM monthly
)
SELECT customer_id, MAX(streak) AS longest_streak
FROM (
  SELECT customer_id, m - rn AS grp, COUNT(*) AS streak
  FROM numbered GROUP BY customer_id, m - rn
) s
GROUP BY customer_id;

Answer

OFFSET does not skip work, it does the work and throws it away. To return rows 100001 to 100020, the database must produce and discard the first hundred thousand rows in sorted order, so cost grows linearly with page number and page 5000 is five thousand times more expensive than page 1. Users never notice because nobody browses to page 5000, but an export job or a crawler paginating through everything turns a fast query into a database incident, and this is a real production story at every Indian e-commerce company.

The fix is keyset pagination, also called seek pagination or cursor pagination: instead of an offset, remember the sort key of the last row you returned and ask for rows strictly after it. With an index on the sort key, every page is an index seek plus twenty rows, so page 5000 costs the same as page 1. The catch is that the sort key must be unique or you skip and duplicate rows at page boundaries, so you use a composite cursor of the real sort column plus the primary key, and the comparison becomes a row value comparison or its expanded OR form.

Keyset pagination cannot jump to an arbitrary page number, only next and previous, which is why UIs that adopt it move to infinite scroll or a 'load more' button. Also note that OFFSET pagination produces duplicated and skipped rows when the underlying data changes between pages, a correctness problem that keyset pagination fixes for free.

/* Slow: scans and discards 100000 rows */
SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC, order_id DESC
LIMIT 20 OFFSET 100000;

/* Keyset: constant cost per page */
SELECT order_id, order_date, total_amount
FROM orders
WHERE (order_date, order_id) < ('2026-06-14 10:22:31', 884213)  /* cursor from last row */
ORDER BY order_date DESC, order_id DESC
LIMIT 20;

/* Expanded form for engines without row value comparison */
WHERE order_date < '2026-06-14 10:22:31'
   OR (order_date = '2026-06-14 10:22:31' AND order_id < 884213)

/* The index that makes it constant time */
CREATE INDEX idx_orders_seek ON orders (order_date DESC, order_id DESC);

Answer

Wrap both updates in an explicit transaction so either both apply or neither does, and lock the rows you are about to modify before you read the balance you will act on. Without the lock, two concurrent transfers from the same wallet can both read a balance of 1000, both decide that a 700 debit is affordable, and both commit, leaving the balance at negative 400. That is a lost update, and it is the single most common concurrency bug in Indian fintech interview scenarios because it maps directly onto a UPI double spend.

SELECT ... FOR UPDATE takes an exclusive row lock, so the second transaction blocks until the first commits and then re-reads the true balance. Three points elevate the answer.

First, lock the accounts in a deterministic order, usually ascending account id, because two transfers in opposite directions that lock in read order will deadlock, and both MySQL InnoDB and PostgreSQL will detect it and kill one transaction with a deadlock error that your application must retry. Second, keep the transaction short: no HTTP calls to a payment gateway inside the lock, because holding a row lock across a network call to NPCI turns a 5 millisecond lock into a 5 second one and the whole system queues behind it. Third, an even better design for money is to make the invariant the database's job, with a CHECK constraint that balance is never negative, so the worst case is a failed transaction instead of a corrupted ledger, and to write an append only ledger table rather than mutating a balance in place.

BEGIN;

/* Lock both rows in a deterministic order to avoid deadlocks */
SELECT account_id, balance
FROM wallets
WHERE account_id IN (1001, 2002)
ORDER BY account_id
FOR UPDATE;

/* Debit only if the funds are actually there */
UPDATE wallets SET balance = balance - 700
WHERE account_id = 1001 AND balance >= 700;
/* application checks affected rows = 1, else ROLLBACK */

UPDATE wallets SET balance = balance + 700
WHERE account_id = 2002;

INSERT INTO ledger (from_account, to_account, amount, created_at)
VALUES (1001, 2002, 700, NOW());

COMMIT;

/* Make the invariant the database's problem */
ALTER TABLE wallets ADD CONSTRAINT chk_balance_nonneg CHECK (balance >= 0);

Answer

For 'customers who placed at least one order', all three forms are semantically equivalent when NULLs are not involved, and modern PostgreSQL and MySQL 8 planners rewrite IN and EXISTS into the same semi join plan, so raw performance folklore from the MySQL 5.x era is out of date. What still differs is behaviour at the edges. IN materialises or scans the subquery's value list, and its NULL semantics are safe in the positive form but catastrophic in the NOT IN form.

EXISTS short circuits on the first matching row and is NULL safe in both the positive and negative forms, which makes it the safer habit. A plain JOIN is the one that is genuinely different: if a customer has five orders, the join produces five rows, so you need DISTINCT to get back to one row per customer, and that DISTINCT costs a sort or hash over the whole result. Worse, adding DISTINCT to fix row multiplication is a smell that hides real duplicates elsewhere in the query, so an interviewer seeing SELECT DISTINCT will often ask why it is there.

The practical rules: use EXISTS when you only want to test existence, use a JOIN when you need columns from the other table, use IN for a small literal list, and never use NOT IN against a nullable column. Prove the choice with EXPLAIN rather than arguing from memory, since the plan depends on table statistics and row counts more than on the keyword you typed.

/* EXISTS: test only, short circuits, NULL safe */
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

/* IN: same plan on modern optimisers, unsafe only in the NOT form */
SELECT c.customer_id, c.name
FROM customers c
WHERE c.customer_id IN (SELECT customer_id FROM orders);

/* JOIN: multiplies rows, needs DISTINCT, but gives you order columns */
SELECT DISTINCT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

/* Need columns AND one row per customer: aggregate instead of DISTINCT */
SELECT c.customer_id, c.name, COUNT(o.order_id) AS orders, MAX(o.order_date) AS last_order
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

Answer

Aggregations only produce rows for combinations that exist in the data, so a state with no orders in June simply has no June row, and a chart built on that result draws a line that skips June rather than dipping to zero. The fix is to build a complete grid first with a CROSS JOIN of the dimension tables, here states cross months, then LEFT JOIN the aggregated facts onto it and COALESCE the missing measures to zero. This is one of the few places where CROSS JOIN is deliberate rather than a bug, and being able to say why is the point of the question.

The grid needs both dimensions to come from somewhere real: distinct states can come from the customers table or better from a reference table, and the month list comes from generate_series in PostgreSQL or a recursive CTE in MySQL. Two cautions. The grid size is the product of the dimensions, so states times months times categories explodes quickly and you should filter the dimension lists before crossing them.

And put the fact table's own filters in the LEFT JOIN's ON clause rather than the WHERE clause, otherwise you drop the zero rows you just worked to create, which is the same ON versus WHERE trap that catches people on simpler LEFT JOINs. This pattern is exactly what a BI or analytics engineering interviewer at Meesho or Swiggy is looking for, because it is the difference between a chart that is correct and one that is merely non-empty.

WITH states AS (
  SELECT DISTINCT state FROM customers WHERE state IS NOT NULL
),
months AS (
  SELECT generate_series('2026-04-01'::date, '2027-03-01'::date, '1 month') AS m
),
grid AS (
  SELECT s.state, m.m FROM states s CROSS JOIN months m
),
facts AS (
  SELECT c.state, DATE_TRUNC('month', o.order_date) AS m, SUM(o.total_amount) AS revenue
  FROM orders o
  JOIN customers c ON c.customer_id = o.customer_id
  WHERE o.status = 'DELIVERED'
  GROUP BY c.state, DATE_TRUNC('month', o.order_date)
)
SELECT g.state, g.m AS month, COALESCE(f.revenue, 0) AS revenue
FROM grid g
LEFT JOIN facts f ON f.state = g.state AND f.m = g.m
ORDER BY g.state, g.m;

Answer

Start with the access method, not the numbers. In MySQL the type column is the headline: ALL is a full table scan, index is a full index scan which is only marginally better, range is an index range scan, ref is an index lookup on a non-unique key, and const or eq_ref is a single row lookup. Anything worse than range on a large table with a selective predicate means an index is missing or has been defeated.

In PostgreSQL the equivalent signal is Seq Scan versus Index Scan or Bitmap Heap Scan. Second, look at the estimated rows against the actual rows, which you only get from EXPLAIN ANALYZE in PostgreSQL or EXPLAIN ANALYZE in MySQL 8.0.18 and later. A large gap means the statistics are stale, and the planner is choosing a nested loop where a hash join was correct, which is the classic cause of a query that was fast last week and is unusable today; the fix is ANALYZE, not a rewrite.

Third, look at the join order and join type: a nested loop over a large outer relation without an index on the inner side is quadratic. Fourth, check Extra in MySQL for Using filesort and Using temporary, which mean the ORDER BY or GROUP BY could not be satisfied by an index. Only after all that do you consider adding an index, and then you check whether an existing composite index would work if you reordered its columns or reordered your predicate, because a fifth index on a hot write table is not free.

/* MySQL */
EXPLAIN
SELECT o.order_id, c.name
FROM orders o JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'DELIVERED' AND o.order_date >= '2026-04-01';

/* id | table | type  | key            | rows    | Extra
    1 | o     | ALL   | NULL           | 4180000 | Using where
    1 | c     | eq_ref| PRIMARY        |       1 |
   type=ALL on 4.18M rows is the problem. */

CREATE INDEX idx_orders_status_date ON orders (status, order_date);
/* re-run: type=range, key=idx_orders_status_date, rows=38210 */

/* PostgreSQL, with real timings */
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;
/* Look for: Seq Scan, rows estimate vs actual, Nested Loop over a big relation */

Answer

A composite index is sorted by the first column, then within equal first values by the second, exactly like a phone book sorted by surname then first name. That gives the leftmost prefix rule: the index can serve a predicate on status alone, or on status and order_date together, but not on order_date alone, because rows for a given date are scattered across every status block. A query filtering only on order_date either scans the whole index or ignores it entirely.

The second rule is that the index stops being usable for further columns after the first range predicate. With status equal to a constant and order_date in a range, both columns are used. With order_date in a range as the first column and status equal to a constant as the second, only the range is used and the status filter becomes a post-filter.

That is why the general design rule is equality columns first, range columns last. A third use worth naming is that the same index can satisfy an ORDER BY order_date when status is pinned to a single value, avoiding a filesort entirely, and can act as a covering index if every column the query needs is in the index, letting MySQL report Using index and skip the table lookup completely. The judgement being tested is whether you would add a second index or reorder the existing one, and the right instinct is usually to reorder or extend rather than proliferate indexes on a write heavy table.

CREATE INDEX idx_o ON orders (status, order_date);

/* USES the index fully: equality on the prefix, range on the second */
SELECT * FROM orders WHERE status = 'DELIVERED' AND order_date >= '2026-04-01';

/* USES the index: leftmost prefix alone */
SELECT * FROM orders WHERE status = 'DELIVERED';

/* CANNOT seek: order_date is not the leftmost column */
SELECT * FROM orders WHERE order_date >= '2026-04-01';

/* Covering index: everything the query needs is in the index */
CREATE INDEX idx_o_cover ON orders (status, order_date, total_amount);
SELECT order_date, total_amount FROM orders WHERE status = 'DELIVERED';
/* MySQL Extra: Using index  -> no table lookup at all */

/* Design rule: equality columns first, range column last */

Answer

Left join the aggregated successful payments onto orders and compare, keeping any row where the totals differ or where no payment exists at all. The subtleties are what make this an advanced question. Aggregate the payments in a subquery before joining, not in the same query as the orders, because joining first and aggregating after multiplies the order total when there are multiple payment rows and produces a reconciliation report that is itself wrong.

Use COALESCE on the payment sum so orders with no payments show a difference equal to the full order amount rather than a NULL that fails every comparison and quietly drops out of the report, which is exactly the case you most want to catch. The rounding trap is the real content: if the amounts are stored as DOUBLE or FLOAT, a comparison of paid to total will report differences of 0.0000001 on thousands of perfectly good orders, because binary floating point cannot represent 0.1 exactly. Money must be DECIMAL, which is exact, and if you are stuck with floats you compare with a tolerance using ABS(diff) greater than 0.01.

Say this before the interviewer prompts, because a fintech panel at Razorpay or PhonePe is asking this question specifically to hear the word DECIMAL. Also handle partial payments and refunds explicitly: if the payments table contains negative refund rows, a naive SUM nets them out and hides a genuine mismatch, so filter or classify by status and type rather than summing everything.

WITH paid AS (
  SELECT order_id,
         SUM(amount)  AS paid_amount,
         COUNT(*)     AS attempts
  FROM payments
  WHERE status = 'SUCCESS'
  GROUP BY order_id
)
SELECT o.order_id,
       o.order_date,
       o.total_amount,
       COALESCE(p.paid_amount, 0) AS paid_amount,
       o.total_amount - COALESCE(p.paid_amount, 0) AS difference,
       COALESCE(p.attempts, 0) AS successful_attempts
FROM orders o
LEFT JOIN paid p ON p.order_id = o.order_id
WHERE o.status IN ('DELIVERED', 'SHIPPED')
  AND ABS(o.total_amount - COALESCE(p.paid_amount, 0)) > 0.01
ORDER BY ABS(o.total_amount - COALESCE(p.paid_amount, 0)) DESC;

/* Money columns must be DECIMAL, never FLOAT or DOUBLE */
ALTER TABLE payments MODIFY amount DECIMAL(12,2) NOT NULL;

/* Why: with DOUBLE, 0.1 + 0.2 = 0.30000000000000004 */

Answer

Cohort retention is three layers and getting the layering right is the entire question. Layer one assigns every customer to a cohort, here the week of their signup_date, truncated so all customers signing up in the same week share a bucket. Layer two computes, for each order, how many whole weeks elapsed between the customer's cohort week and the order week, which is a date difference divided into weeks rather than a raw day count, because a customer who signs up Saturday and orders Monday is in week 1, not week 0, under a calendar week definition.

Layer three counts distinct customers per cohort per week offset and divides by the cohort size, and the cohort size must come from the cohort table, not from the orders, because customers who never ordered again still belong in the denominator; using the order derived count is the classic error that makes every cohort look like 100 percent retained in week 0. Present the result as a triangle, cohort weeks down the side and week offsets across, which means pivoting with conditional aggregation. Two details senior interviewers probe: whether you use DISTINCT customers rather than order counts, since a customer placing three orders in week 2 is still one retained customer, and whether you handle the ragged edge, because the most recent cohort has not had four weeks to convert and its later cells must be NULL rather than zero or the chart implies a collapse in retention that is really just missing time.

WITH cohorts AS (
  SELECT customer_id, DATE_TRUNC('week', signup_date) AS cohort_week
  FROM customers
),
sizes AS (
  SELECT cohort_week, COUNT(*) AS cohort_size FROM cohorts GROUP BY cohort_week
),
activity AS (
  SELECT c.cohort_week,
         FLOOR(EXTRACT(EPOCH FROM (DATE_TRUNC('week', o.order_date) - c.cohort_week))
               / 604800) AS week_offset,
         o.customer_id
  FROM orders o
  JOIN cohorts c ON c.customer_id = o.customer_id
  WHERE o.status = 'DELIVERED'
),
retained AS (
  SELECT cohort_week, week_offset, COUNT(DISTINCT customer_id) AS retained
  FROM activity
  WHERE week_offset BETWEEN 0 AND 4
  GROUP BY cohort_week, week_offset
)
SELECT s.cohort_week, s.cohort_size,
       ROUND(100.0 * MAX(CASE WHEN r.week_offset = 1 THEN r.retained END) / s.cohort_size, 1) AS wk1_pct,
       ROUND(100.0 * MAX(CASE WHEN r.week_offset = 2 THEN r.retained END) / s.cohort_size, 1) AS wk2_pct,
       ROUND(100.0 * MAX(CASE WHEN r.week_offset = 3 THEN r.retained END) / s.cohort_size, 1) AS wk3_pct,
       ROUND(100.0 * MAX(CASE WHEN r.week_offset = 4 THEN r.retained END) / s.cohort_size, 1) AS wk4_pct
FROM sizes s
LEFT JOIN retained r ON r.cohort_week = s.cohort_week
GROUP BY s.cohort_week, s.cohort_size
ORDER BY s.cohort_week;

Answer

Different row counts across concurrent runs is a phantom read or a non-repeatable read, and which one it is tells you what to change. Under READ COMMITTED, which is the PostgreSQL default, every statement sees a fresh snapshot, so two SELECTs inside one transaction can legitimately return different rows if another transaction committed in between. Under REPEATABLE READ, which is the MySQL InnoDB default, the transaction takes a consistent snapshot at the first read and every subsequent read sees that same snapshot, so the counts are stable within the transaction.

This is why a long running report that issues five queries can produce internally inconsistent numbers on PostgreSQL with autocommit and consistent ones on MySQL, and the fix is not to change engines but to run the report inside an explicit transaction at REPEATABLE READ, or in PostgreSQL to use a REPEATABLE READ transaction, so every query in the report reads the same snapshot. Two further points. InnoDB's REPEATABLE READ prevents phantoms for plain SELECTs via MVCC and uses gap locks for locking reads, which is stronger than the SQL standard requires but also the source of surprising lock waits on range updates.

And SERIALIZABLE is available on both but costs throughput, in PostgreSQL through serialization failures your application must retry, so it is the right answer for a financial invariant and the wrong answer for a dashboard. The diagnostic step to state first is confirming it really is concurrency and not a non-deterministic ORDER BY with LIMIT, which produces different rows for a completely different reason.

/* Make a multi query report internally consistent */

/* PostgreSQL */
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT COUNT(*) FROM orders WHERE status = 'DELIVERED';
SELECT SUM(total_amount) FROM orders WHERE status = 'DELIVERED';
COMMIT;

/* MySQL, REPEATABLE READ is already the default */
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT COUNT(*) FROM orders WHERE status = 'DELIVERED';
COMMIT;

/* Check what you are actually running */
SELECT @@transaction_isolation;              /* MySQL */
SHOW transaction_isolation;                  /* PostgreSQL */

/* Anomalies permitted by level
   READ UNCOMMITTED : dirty, non-repeatable, phantom
   READ COMMITTED   : non-repeatable, phantom
   REPEATABLE READ  : phantom (standard), InnoDB blocks most via MVCC
   SERIALIZABLE     : none
*/

Answer

This chains two window functions over the same partition. LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) gives the previous order date on each row, and the difference is the gap. To detect a doubling you need the previous gap too, which is LAG applied to the gap itself, and you cannot nest a window function inside another window function in the same select list, so the gap must be computed in one CTE and lagged in the next.

Candidates who try LAG(LAG(order_date) OVER (...)) OVER (...) get a syntax error, and knowing the layering rule is what the question tests. The interpretation matters as much as the mechanics. A doubling gap is a churn signal, so the business wants the most recent gaps weighted more heavily than a doubling that happened two years ago, which means filtering to the latest one or two intervals per customer with a ROW_NUMBER over order_date descending.

Customers with fewer than three orders cannot have a gap comparison at all and produce NULLs, and those NULLs should be excluded rather than treated as no change. If timestamps are involved, decide whether the gap is in whole days or fractional days, because two orders four hours apart is a gap of zero whole days and dividing by zero when computing a ratio will either error in PostgreSQL or produce NULL in MySQL, so guard with NULLIF. Ordering by the ratio descending gives the churn team a ranked worklist, which is the actual deliverable.

WITH gaps AS (
  SELECT customer_id, order_id, order_date,
         order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS gap
  FROM orders
  WHERE status = 'DELIVERED'
),
compared AS (
  SELECT customer_id, order_id, order_date, gap,
         LAG(gap) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_gap,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS recency
  FROM gaps
)
SELECT customer_id, order_date, prev_gap, gap,
       ROUND(gap / NULLIF(prev_gap, 0), 2) AS gap_ratio
FROM compared
WHERE recency = 1
  AND prev_gap IS NOT NULL
  AND gap >= prev_gap * 2
ORDER BY gap_ratio DESC;

/* LAG cannot be nested directly, this is a syntax error:
   LAG(LAG(order_date) OVER (...)) OVER (...)
   Compute in one CTE, lag it in the next. */

Answer

Diagnose before optimising, in a fixed order. First confirm what changed: was it data volume, a schema change, a new index, a plan flip, or contention from another job that now overlaps. Compare the current EXPLAIN ANALYZE against what the plan used to be, and check whether statistics are stale, because a plan flip from hash join to nested loop after a bad estimate looks exactly like a volume problem but is fixed by ANALYZE in seconds.

Second, check whether the query is doing work it does not need: SELECT star pulling wide columns, a DISTINCT masking a join fan-out, a correlated subquery per row, or a full year scanned when the job only needs yesterday. Incremental aggregation, where the job processes only the new partition and merges into a summary table with an upsert, is usually the single biggest win and turns an O(all history) job into an O(one day) job. Third, indexing: a covering index on the filter and group by columns can turn a scan plus filesort into an index-only scan.

Fourth, structural changes: range partitioning by month so the planner prunes to one partition, or a pre-aggregated summary table refreshed nightly, or a materialised view in PostgreSQL with a concurrent refresh so readers are not blocked. Fifth, move it: if the workload is genuinely analytical, running it against a read replica or a columnar store rather than the transactional primary is the correct architectural answer, and saying so shows you know when SQL tuning has hit its ceiling. Do these in order and stop at the first one that works.

/* 1. Is it stale statistics or a real plan change */
ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

/* 2. Incremental instead of full rebuild */
INSERT INTO daily_revenue (summary_date, revenue)
SELECT order_date::date, SUM(total_amount)
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 day'
  AND order_date <  CURRENT_DATE
GROUP BY order_date::date
ON CONFLICT (summary_date) DO UPDATE SET revenue = EXCLUDED.revenue;

/* 3. Covering index so the group by needs no table lookup */
CREATE INDEX idx_orders_agg ON orders (order_date, status) INCLUDE (total_amount);

/* 4. Range partition so the planner prunes */
CREATE TABLE orders_2026_08 PARTITION OF orders
  FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

/* 5. Materialised view, refresh without blocking readers */
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue;

Answer

A CTE names an intermediate result for readability within a single statement. In PostgreSQL before version 12 a CTE was an optimisation fence, always materialised, which people exploited deliberately to stop the planner inlining an expensive subquery. From version 12 CTEs are inlined by default when referenced once and not recursive, and you get the old behaviour back explicitly with MATERIALIZED or force inlining with NOT MATERIALIZED.

MySQL 8 also inlines or materialises based on its own heuristics. Knowing this version detail is a strong senior signal, because a query that got 40 times slower after a Postgres 12 upgrade is usually a CTE that stopped being a fence. A subquery is the same thing without the name, and a correlated subquery is materially different because it executes per outer row unless the planner rewrites it.

A temporary table genuinely persists the intermediate result for the session, which is the right tool when you reference the same intermediate three or four times, when you want to index the intermediate, or when the transformation spans multiple statements, and its cost is real writes plus catalogue churn. A materialised view stores the result on disk across sessions and is refreshed on a schedule, so it is the right answer for an expensive aggregate that many dashboards read and that can tolerate being a few minutes stale; PostgreSQL supports REFRESH MATERIALIZED VIEW CONCURRENTLY which needs a unique index but does not block readers, while MySQL has no materialised views at all and you emulate them with a summary table plus a scheduled upsert job.

/* CTE: readability, inlined by default in PostgreSQL 12+ */
WITH recent AS (
  SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT customer_id, COUNT(*) FROM recent GROUP BY customer_id;

/* Force the old fence behaviour when the planner makes a bad choice */
WITH recent AS MATERIALIZED ( SELECT ... )

/* Temp table: referenced many times, and you can index it */
CREATE TEMPORARY TABLE tmp_recent AS
  SELECT * FROM orders WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';
CREATE INDEX ON tmp_recent (customer_id);

/* Materialised view: expensive aggregate, many readers, staleness acceptable */
CREATE MATERIALIZED VIEW mv_customer_ltv AS
  SELECT customer_id, SUM(total_amount) AS ltv FROM orders GROUP BY customer_id;
CREATE UNIQUE INDEX ON mv_customer_ltv (customer_id);   /* required for CONCURRENTLY */
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_ltv;

/* MySQL has no materialised views, use a summary table plus a scheduled upsert */

Answer

A single GROUP BY can give you revenue per state per category, but it cannot tell you which of those rows is the maximum per state, because MAX is an aggregate over values and there is no aggregate that returns the other columns of the row that produced the maximum. Writing SELECT state, category, MAX(revenue) with GROUP BY state either errors under ONLY_FULL_GROUP_BY or, in a permissive MySQL configuration, returns the maximum revenue paired with an arbitrary category, which is a silently wrong answer that reaches a dashboard. This is the argmax problem and it needs two levels: aggregate to state and category in the first level, then rank within state in the second and keep rank one.

Use ROW_NUMBER if the business wants exactly one category per state and accepts an arbitrary winner on an exact tie, and RANK or DENSE_RANK if tied categories should both be shown, and add a deterministic tie breaker either way. PostgreSQL offers a shortcut worth naming, DISTINCT ON (state) with an ORDER BY state, revenue DESC, which is both shorter and fast. Two extensions the interviewer usually asks for next: the winning category's share of the state's total revenue, which is the row's revenue divided by SUM(revenue) OVER (PARTITION BY state) computed in the same pass, and the runner up, which is just rank two from the same ranked CTE, so structuring the query with a ranked CTE means both follow ups are a one line change.

WITH state_category AS (
  SELECT c.state,
         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 customers c ON c.customer_id = o.customer_id
  JOIN products  p ON p.product_id  = oi.product_id
  WHERE o.status = 'DELIVERED'
  GROUP BY c.state, p.category
),
ranked AS (
  SELECT sc.*,
         ROW_NUMBER() OVER (PARTITION BY state ORDER BY revenue DESC, category) AS rn,
         SUM(revenue) OVER (PARTITION BY state) AS state_revenue
  FROM state_category sc
)
SELECT state, category AS top_category, revenue,
       ROUND(100.0 * revenue / state_revenue, 1) AS share_pct
FROM ranked
WHERE rn = 1
ORDER BY revenue DESC;

/* BROKEN: category is not determined by the grouping */
/* SELECT state, category, MAX(revenue) FROM state_category GROUP BY state; */

Answer

A single UPDATE touching a hundred million rows holds row locks for the entire duration, generates an enormous amount of undo in InnoDB or WAL in PostgreSQL, and replicates as one giant transaction, so read replicas fall minutes or hours behind and every dashboard reading them goes stale. In PostgreSQL it also bloats the table, because an UPDATE writes a new row version and leaves the old one for autovacuum, so a full table update can nearly double the table size on disk before vacuum catches up. If the statement fails at 90 percent, the rollback itself can take longer than the update did.

The safe pattern is batching: loop over a bounded key range, update a few thousand rows at a time, commit each batch, and pause briefly between batches so replication and vacuum can keep up. Drive the batches off the primary key rather than off the column you are setting, because a WHERE new_col IS NULL predicate scans an ever growing already-done region unless it is indexed, and adding an index just for the backfill is often worth it and then dropped. Make the loop resumable by recording the last processed id, so a failure restarts from where it stopped rather than from zero.

Two more production practices: run it during a low traffic window, which in Indian consumer traffic means early morning IST rather than midnight, and monitor replica lag as the loop runs, slowing the batch rate if lag climbs. Finally, add the column as nullable with no default first, because in older engines adding a NOT NULL column with a default rewrote the whole table under a lock.

/* 1. Add the column cheaply, nullable, no default */
ALTER TABLE orders ADD COLUMN order_month DATE NULL;

/* 2. Batched, resumable backfill (PostgreSQL) */
DO $$
DECLARE
  last_id BIGINT := 0;
  rows_done INT;
BEGIN
  LOOP
    UPDATE orders o
    SET order_month = DATE_TRUNC('month', o.order_date)::date
    WHERE o.order_id > last_id
      AND o.order_id <= last_id + 5000;
    GET DIAGNOSTICS rows_done = ROW_COUNT;
    last_id := last_id + 5000;
    COMMIT;
    EXIT WHEN last_id > (SELECT MAX(order_id) FROM orders);
    PERFORM pg_sleep(0.05);   /* let replicas and vacuum catch up */
  END LOOP;
END $$;

/* 3. Only after the backfill, enforce the constraint */
ALTER TABLE orders ALTER COLUMN order_month SET NOT NULL;

/* Watch this while it runs */
SELECT client_addr, replay_lag FROM pg_stat_replication;