Business Analyst Interview Questions — SQL

“Top customers” has at least six defensible readings, and writing SQL before narrowing it means a rewrite. Four questions do most of the work.

By what measure? Revenue, gross margin, order count, growth. A stationery wholesaler’s highest-revenue account can be its lowest-margin one, and which list you produce changes who gets a call.

Over what period? This financial year, trailing twelve months, all time. All-time favours old accounts that stopped buying.

At what grain? A customer might be a billing account, a parent company, or a delivery location. A group with fourteen branch accounts looks small at one grain and enormous at another.

What is it for? This is the question that resolves the others. A retention campaign wants recent revenue with a churn risk lens. An account manager reshuffle wants margin. A press release wants something else again. Ask it first and the rest often answers itself.

Two more that catch people out: which statuses count, since cancelled and returned orders are frequently left in by accident, and whether the figure should be net of returns and taxes.

Then restate before building:

Top 20 customers by net revenue, at parent company level, for April 2026 to date, excluding cancelled orders and net of returns, for the retention campaign.

That sentence takes a minute to write and catches the mismatch while it is still cheap. Sending it as a message also creates a record, which matters when the number is questioned later.

If they cannot answer, do not stall. Pick the most defensible interpretation, produce it quickly, and state the assumption on the output itself. A fast answer with visible assumptions gets corrected in one round. A perfect answer three days later has usually been overtaken.

Interviewers ask this because most analytical rework comes from unasked questions, not from bad SQL.

Start with the query that breaks:

SELECT patient_id,
       DATEDIFF(discharge_date, admit_date) AS stay_days
FROM admissions
WHERE stay_days > 7;

Unknown column stay_days. The column is right there on line two, and the database still cannot see it. The reason is ordering.

The logical sequence is:

1. FROM / JOIN — assemble the working row set
2. WHERE — filter individual rows
3. GROUP BY — collapse rows into groups
4. HAVING — filter groups
5. SELECT — evaluate expressions and assign aliases
6. DISTINCT
7. ORDER BY — sort
8. LIMIT / OFFSET / TOP / FETCH

Aliases come into existence at step 5. WHERE ran back at step 2, three steps too early. ORDER BY sits at step 7, which is why ORDER BY stay_days works perfectly in the same query that rejects it in WHERE.

Two ways out: repeat the expression in WHERE, or push the calculation into a derived table or CTE.

SELECT patient_id, stay_days
FROM (
  SELECT patient_id,
         DATEDIFF(discharge_date, admit_date) AS stay_days
  FROM admissions
) a
WHERE stay_days > 7;

Vendors diverge on the middle of that list, and it catches people who switch databases. MySQL permits aliases in GROUP BY and HAVING. PostgreSQL permits them in GROUP BY and ORDER BY, but not in HAVING. SQL Server permits them only in ORDER BY. None of them permit an alias in WHERE. A query written and tested on MySQL can fail on its first run against SQL Server for exactly this reason.

The word “logical” is doing real work here. This is the order the language guarantees results are equivalent to, not a description of the execution plan. An optimiser is free to push a filter below a join, evaluate a scalar subquery once instead of per row, or skip a sort it can satisfy from an index. Interviewers ask this because the sequence quietly explains WHERE versus HAVING, alias scope, and why HAVING can reference aggregates.

NULL is not a value sitting in the cell. It is a marker saying the value is unknown. So settled_on = NULL is asking whether one unknown quantity equals another unknown quantity, and the honest answer is: unknown. WHERE keeps a row only when the condition is true, and unknown is not true.

SELECT claim_id FROM claims WHERE settled_on = NULL;   -- always empty
SELECT claim_id FROM claims WHERE settled_on IS NULL;  -- pending claims

The same logic makes NULL <> NULL empty too, so you cannot escape by flipping the operator.

Where this genuinely costs people money is NOT IN. Suppose you want policies that have never been claimed against:

SELECT policy_id
FROM policies
WHERE policy_id NOT IN (SELECT policy_id FROM claims);

If a single row in claims has a NULL policy_id, this returns nothing. The reason: x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL, and that last term is unknown, so the whole expression can never be true. Nothing errors out. You just get an empty result and assume every policy has a claim. NOT EXISTS, or a LEFT JOIN with IS NULL, behaves correctly here.

One inconsistency to keep straight: NULL is not equal to itself for comparison, but GROUP BY and DISTINCT treat all NULLs as a single group, and ORDER BY sorts them together. Most engines also allow multiple NULLs in a UNIQUE column for the same reason.

When you do need NULL-safe equality, PostgreSQL and the standard offer IS DISTINCT FROM, and MySQL has the <=> operator.

The real payoff is conditional aggregation. Here are recharge volumes bucketed by ticket size, per telecom circle, in a single pass:

SELECT circle,
       COUNT(*) AS recharges,
       SUM(CASE WHEN amount < 100 THEN 1 ELSE 0 END)               AS under_100,
       SUM(CASE WHEN amount >= 100 AND amount < 500 THEN 1 ELSE 0 END) AS mid,
       SUM(CASE WHEN amount >= 500 THEN 1 ELSE 0 END)              AS premium,
       SUM(CASE WHEN validity_days >= 84 THEN amount ELSE 0 END)   AS long_pack_revenue
FROM recharges
WHERE recharged_on >= '2026-07-01'
GROUP BY circle;

Three separate queries collapse into one, and the table is scanned once.

Now the trap, and it is a common one:

COUNT(CASE WHEN amount < 100 THEN 1 ELSE 0 END)   -- counts every row

COUNT ignores NULL, not zero. The ELSE branch hands it a 0, which is a perfectly good value, so every row gets counted and all three buckets come back identical to the row total. Either drop the ELSE so non-matching rows produce NULL, or keep the ELSE and use SUM. Pick one style and stay with it.

Second thing to watch: CASE stops at the first branch that evaluates to true. Write overlapping ranges in a careless order and rows land in the wrong bucket without any warning — a WHEN amount > 100 sitting above WHEN amount > 500 sends every premium recharge into the mid bucket.

Also worth knowing that simple CASE (CASE status WHEN 'ACTIVE' THEN ...) uses equality internally, so it can never match a NULL status. Searched CASE with an explicit WHEN status IS NULL branch is the way to handle those.

Pick the join by asking which rows you cannot afford to lose. Take flights (every scheduled departure for a day) and bookings.

• INNER JOIN keeps only flights that have at least one booking. Empty flights disappear from the result. Fine for revenue analysis, quietly wrong for a load-factor report, because the worst-performing flights are exactly the ones that vanish.
• LEFT JOIN keeps every flight, filling booking columns with NULL where there is no match. This is the one you want for any report where the left table defines the universe of things being measured.
• RIGHT JOIN is the same operation with the tables reversed. It is legal and it works, but most teams standardise on LEFT and reorder the tables instead, because a query mixing both directions is genuinely hard to read.
• FULL OUTER JOIN keeps unmatched rows from both sides at once. Its natural home is reconciliation: in one pass you see flights with no bookings and bookings pointing at a flight ID that no longer exists in the schedule. Data quality checks between two systems are where this earns its keep.

MySQL has no FULL OUTER JOIN. You emulate it:

SELECT f.flight_id, b.booking_id FROM flights f
LEFT JOIN bookings b ON b.flight_id = f.flight_id
UNION
SELECT f.flight_id, b.booking_id FROM flights f
RIGHT JOIN bookings b ON b.flight_id = f.flight_id;

UNION rather than UNION ALL, since the matched rows appear in both halves.

The related pattern interviewers usually probe next is the anti-join: a LEFT JOIN with WHERE b.booking_id IS NULL gives you flights nobody booked.

Here is the query, joining e-commerce orders to a sparse returns table:

SELECT o.order_id, r.return_reason
FROM orders o
LEFT JOIN returns r ON r.order_id = o.order_id
WHERE r.return_reason = 'DAMAGED';

The join does its job. Every order survives it, and orders that were never returned come out with return_reason set to NULL. Then WHERE runs, and NULL = 'DAMAGED' evaluates to unknown, so every one of those padded rows is discarded. What remains is precisely the inner join. You have written LEFT JOIN and received INNER JOIN semantics, with no warning of any kind.

The fix depends on what you meant.

If you wanted all orders, with the reason shown only when it was a damaged return, the condition restricts matching, so it belongs in ON:

SELECT o.order_id, r.return_reason
FROM orders o
LEFT JOIN returns r
  ON r.order_id = o.order_id
 AND r.return_reason = 'DAMAGED';

Every order stays. Orders with a non-damaged return simply fail the match and get NULL, same as orders with no return at all.

If you genuinely wanted only damaged returns, use an INNER JOIN and say so, so the next person reading the query is not misled.

There is one right-side condition that is safe in WHERE, and it is the exception that proves the rule: WHERE r.order_id IS NULL. That is true only for padded rows, so it deliberately keeps the non-matches and throws away the matches. It is the standard anti-join.

The reason this matters beyond the interview is how quietly it fails. A monthly report joining orders to a partially populated feedback table with WHERE f.rating >= 4 does not error. It returns a smaller, entirely plausible order count, and it can sit in a dashboard for two quarters before anyone reconciles it against the source system.

Working rule: conditions on the preserved table go in WHERE, conditions on the optional table go in ON. For an INNER JOIN the distinction makes no difference to the result, which is where the habit of putting everything in WHERE comes from.

A gym chain has members, plus payments and class_bookings, both with many rows per member. This query looks reasonable:

SELECT m.member_id, SUM(p.amount) AS total_paid
FROM members m
JOIN payments p        ON p.member_id = m.member_id
JOIN class_bookings cb ON cb.member_id = m.member_id
GROUP BY m.member_id;

A member with 6 payments and 20 class bookings produces 6 × 20 = 120 rows, and SUM(p.amount) comes out 20 times too high. The nasty part is that the inflation factor differs per member, so total revenue is not off by a clean multiple you might notice. It is just wrong, in a way that looks like it could be right.

A join is not a lookup. It pairs every left row with every right row that satisfies the condition. One join to a many-side is often exactly what you want: joining orders to order_items should repeat the order row once per item. The damage starts when you hang two independent one-to-many branches off the same key, because they form a Cartesian product with each other inside every member.

Diagnose before you assume. Compare the row count to the base table, or check the join key directly:

SELECT member_id, COUNT(*) FROM payments GROUP BY member_id HAVING COUNT(*) > 1;

The reliable fix is to collapse each branch to one row per key before joining:

SELECT m.member_id, p.total_paid, cb.classes_booked
FROM members m
LEFT JOIN (SELECT member_id, SUM(amount) AS total_paid
           FROM payments GROUP BY member_id) p  ON p.member_id = m.member_id
LEFT JOIN (SELECT member_id, COUNT(*) AS classes_booked
           FROM class_bookings GROUP BY member_id) cb ON cb.member_id = m.member_id;

Resist the shortcut of SUM(DISTINCT p.amount). Two genuine payments of ₹2,499 in different months are indistinguishable after DISTINCT, so one of them silently disappears. It patches the number without fixing the cardinality, which is worse than leaving it visibly broken.

Worth doing in the interview itself: before writing anything, ask whether the relationship is one-to-one or one-to-many. Candidates who ask that get read as people who have debugged a wrong revenue figure at least once.

One query on a music streaming log makes all three visible:

SELECT COUNT(*)                 AS play_events,     -- 4,812,000
       COUNT(user_id)           AS identified_plays,-- 4,105,000
       COUNT(DISTINCT user_id)  AS listeners,       -- 318,400
       COUNT(DISTINCT track_id) AS tracks_played    -- 27,900
FROM plays
WHERE played_on >= '2026-08-01';

Four different numbers, four different questions. The gap between the first two is anonymous playback with no logged-in user. The gap between the second and third is simply that people listen more than once.

Where this turns into a bug is after a LEFT JOIN. Count the wrong side and you count the padding:

SELECT a.artist_id, COUNT(*) AS plays
FROM artists a
LEFT JOIN plays p ON p.artist_id = a.artist_id
GROUP BY a.artist_id;

An artist nobody streamed still contributes one NULL-padded row, so COUNT(*) reports 1 play instead of 0. COUNT(p.play_id) reports 0, because the padded row has NULL there. Any time a count follows an outer join, count a column from the optional table, never the star.

On performance, the folklore that COUNT(*) is slower than COUNT(id) is worth dropping. In PostgreSQL and MySQL/InnoDB, COUNT(*) is treated as a row count and the planner picks the cheapest index it can scan. COUNT(id) may be no faster and cannot use certain shortcuts if the column is nullable. What genuinely costs is COUNT(DISTINCT ...), which has to deduplicate through a hash or a sort, and gets expensive on high-cardinality columns. Approximate functions exist for that, such as APPROX_COUNT_DISTINCT in SQL Server and BigQuery, when an exact figure is not needed.

Multi-column distinct is a portability trap. MySQL accepts COUNT(DISTINCT user_id, track_id). PostgreSQL requires COUNT(DISTINCT (user_id, track_id)) with a row constructor, and SQL Server supports neither, so you concatenate or use a subquery.

There is no hierarchy in a GROUP BY list. The database builds a key out of every grouped column, joined together, and every distinct key becomes one output row. GROUP BY market, crop and GROUP BY crop, market produce the same set of rows with the same aggregates. Only the column order in a SELECT or an ORDER BY changes what you see.

For daily mandi arrivals:

SELECT market_name,
       crop,
       SUM(quantity_quintal)   AS arrivals,
       AVG(modal_price_per_qtl) AS avg_price
FROM mandi_arrivals
WHERE trade_date = '2026-08-19'
GROUP BY market_name, crop;

If Bengaluru’s Yeshwanthpur mandi traded onion, tomato and potato that day, that mandi contributes three rows. A mandi that traded only onion contributes one. There is no fixed rectangle, only the combinations that actually occur in the filtered data.

That last point is the one that trips reports. GROUP BY cannot invent a group for a pair with no rows behind it. A crop that arrived at no mandi on that date is absent from the output, not present with a zero. If the consumer of your report needs a fixed set of rows, you build the grid separately and join to it.

The counting rule is worth internalising because it explains a class of “my numbers changed” tickets. Adding a column to GROUP BY can only split existing groups, never merge them. Someone adds grade to the list for a bit more detail, the row count goes from 900 to 2,600, and the totals per market are now spread across three grade rows each. Nothing is wrong. The report just answers a finer question than the one the dashboard tile was built for.

Every non-aggregated column in the SELECT must appear in the GROUP BY. PostgreSQL and SQL Server enforce this. MySQL enforces it too under ONLY_FULL_GROUP_BY, which is on by default from 5.7 onwards, but a legacy server with that mode disabled will accept a stray column and hand back a value from an arbitrary row in the group. The one relaxation everyone allows: columns functionally dependent on the grouped primary key can be omitted in PostgreSQL and modern MySQL.

Both of these find pharmacy SKUs whose current stock is below the average stock level for their category. The nested version:

SELECT s.sku_id, s.category, s.on_hand
FROM stock s
WHERE s.on_hand < (SELECT AVG(on_hand) FROM stock x WHERE x.category = s.category);

The CTE version:

WITH category_avg AS (
  SELECT category, AVG(on_hand) AS avg_on_hand
  FROM stock
  GROUP BY category
)
SELECT s.sku_id, s.category, s.on_hand
FROM stock s
JOIN category_avg c ON c.category = s.category
WHERE s.on_hand < c.avg_on_hand;

The second one reads in the order you would explain it aloud. Compute the category averages, then compare each SKU against them. That matters more than it sounds, because the version you can debug in six months is the version that stays correct. You can also comment out the final SELECT and run the CTE alone, which you cannot do with a deeply nested inline subquery.

Reuse is where a CTE stops being a style preference. Reference category_avg twice, once for understocked SKUs and once for overstocked, and the subquery version means writing the same aggregation twice and hoping both copies get edited together.

On plans, be careful about the advice you repeat. PostgreSQL before version 12 always materialised CTEs, which made WITH an optimisation fence: predicates could not be pushed down into it, and a CTE inside a big query was sometimes a real performance problem. From 12 onwards a CTE used once and not recursive is inlined by default, and you can force either behaviour with MATERIALIZED or NOT MATERIALIZED. MySQL 8.0 also merges CTEs into the outer query where it can. So on current versions the two forms above typically produce the same plan. The correct answer in the room is that it depends on the engine and version, and that you would check EXPLAIN rather than assert it.

One capability a subquery simply does not have: recursion. Walking an unbounded hierarchy or generating a series needs WITH RECURSIVE, which is CTE-only.

For the positive check, pick whichever reads better:

-- accounts that received a transaction flagged for review
SELECT a.account_no FROM accounts a
WHERE a.account_no IN (SELECT account_no FROM flagged_txns);

SELECT a.account_no FROM accounts a
WHERE EXISTS (SELECT 1 FROM flagged_txns f WHERE f.account_no = a.account_no);

Same result, and on PostgreSQL and MySQL 8.0 usually the same plan, because both get rewritten into a semi-join. The advice that “EXISTS is faster than IN” comes from older optimisers, particularly MySQL 5.x, where a correlated IN subquery could be re-executed per outer row. Repeating it as a general truth about current engines is how candidates get caught out.

Negate the check and the two stop being interchangeable:

SELECT a.account_no FROM accounts a
WHERE a.account_no NOT IN (SELECT account_no FROM flagged_txns);

If flagged_txns.account_no is nullable and even one row is NULL, this returns zero accounts. NOT IN expands into a chain of inequality comparisons joined by AND, and a comparison against NULL is never true, so the chain can never succeed. The query does not fail. It reports that every account has a flagged transaction, which in a compliance report is a conclusion someone might act on.

NOT EXISTS has no such behaviour. It asks whether a matching row exists, gets a straight yes or no, and is the form to write by default:

SELECT a.account_no FROM accounts a
WHERE NOT EXISTS (SELECT 1 FROM flagged_txns f WHERE f.account_no = a.account_no);

Two smaller points worth having ready. IN with a literal list is a different thing from IN with a subquery, and very long literal lists, in the tens of thousands, degrade badly and are better handled by a temporary table or a join. And the SELECT 1 inside EXISTS is convention rather than optimisation. The engine never evaluates the select list, so SELECT * performs identically, but SELECT 1 signals to the reader that no column is being returned.

GROUP BY answers “what is the total per category” and hands back one row per category. The moment you also want the individual rows, you are asking a different question and GROUP BY is the wrong tool.

For a supermarket’s daily till log:

SELECT bill_id,
       aisle,
       line_total,
       SUM(line_total) OVER (PARTITION BY aisle)                       AS aisle_total,
       ROUND(100.0 * line_total / SUM(line_total) OVER (PARTITION BY aisle), 2) AS pct_of_aisle
FROM bill_lines
WHERE bill_date = '2026-08-19';

Every line item survives, and each one now carries its aisle total and its share of that total. Doing this with GROUP BY needs an aggregate in a subquery joined back to the detail, which is three times the text for the same result.

The clause-order rule catches nearly everyone once. Window functions are evaluated after WHERE, GROUP BY and HAVING, and before ORDER BY. Two consequences follow. First, the window only ever sees rows that survived WHERE, so filtering to one aisle makes the “total” the total of that aisle alone. Second, you cannot filter on a window result in WHERE:

WHERE SUM(line_total) OVER (PARTITION BY aisle) > 50000   -- error, every engine

The value does not exist yet at that point. Wrap the query in a CTE or derived table and filter on the outer level. That single restriction is behind most “top N per group” solutions you will see.

Windows and GROUP BY are not mutually exclusive. A window function can operate on the output of a GROUP BY, since it runs later:

SELECT aisle, SUM(line_total) AS aisle_total,
       SUM(SUM(line_total)) OVER () AS store_total
FROM bill_lines GROUP BY aisle;

The nested SUM looks wrong on first reading and is correct. The inner one aggregates the group, the outer one is a window over the grouped rows.

OVER () with nothing inside treats the entire result set as one partition, which is the neat way to put a grand total on every row for percentage calculations.

Take five cricket batters ordered by runs scored in a tournament, with two of them level on 412:

SELECT player, runs,
       ROW_NUMBER() OVER (ORDER BY runs DESC) AS rn,
       RANK()       OVER (ORDER BY runs DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY runs DESC) AS dense
FROM tournament_stats;

Read the Bose row. RANK says 4th because two players occupy second place and third is consumed. DENSE_RANK says 3rd because it counts distinct scores, not positions. Neither is more correct; they answer different questions. A leaderboard shown to the public almost always wants RANK, because “joint second, then fourth” is how sport reports it. DENSE_RANK is what you want for “the third highest distinct score”, which is a different claim entirely.

The trap with ROW_NUMBER is that it fabricates an order where none exists. Naik and Rathore are genuinely level, and ROW_NUMBER hands one of them 2 and the other 3. Which one is not defined, and it can change between runs, after an index rebuild, or when the query goes parallel. If you use ROW_NUMBER to pick “the top scorer” and there is a tie, you have written a query that returns different answers on different days.

The fix is to make the ordering deterministic by adding a tiebreaker that is unique:

ROW_NUMBER() OVER (ORDER BY runs DESC, player_id)

Now it is arbitrary but stable, which is what you want for deduplication and pagination.

ROW_NUMBER is still the right choice for exactly those jobs, where you need one row per group and do not care which. Deduplication, “latest record per key”, and stable paging all want distinct numbers and no gaps.

ORDER BY inside OVER is required for RANK and DENSE_RANK, since there is nothing to rank without it. ROW_NUMBER technically permits its absence in some engines, but the result is then non-deterministic and there is no good reason to write it.

Before writing anything, ask the interviewer one question: if two events are priced identically at the top, is the next price down the second highest or the third? People who start typing immediately give the wrong answer half the time, and the asking is a large part of what is being marked.

Prices for concert tickets: 4500, 4500, 3200, 2800.

-- third highest DISTINCT price -> 2800
SELECT price FROM (
  SELECT price, DENSE_RANK() OVER (ORDER BY price DESC) AS dr
  FROM ticket_prices
) t WHERE dr = 3;

-- third row by price -> 3200
SELECT price FROM ticket_prices ORDER BY price DESC LIMIT 1 OFFSET 2;

Two defensible answers, four hundred rupees apart. “Third highest price” in ordinary speech nearly always means the third distinct price, so DENSE_RANK is the safer default, but say why you chose it.

The window function has to be filtered in an outer query. WHERE DENSE_RANK() OVER (...) = 3 is rejected everywhere, because windows are evaluated after WHERE.

Two edge cases worth raising unprompted. If the table holds only two distinct prices, both queries return zero rows rather than NULL, so calling code that expects a scalar needs to handle an empty result. And NULL prices sort first under ORDER BY price DESC in PostgreSQL and last in MySQL, which shifts every position by one on a nullable column. WHERE price IS NOT NULL removes the ambiguity and costs nothing.

The pre-window classic still comes up on older MySQL:

SELECT MAX(price) FROM ticket_prices
WHERE price < (SELECT MAX(price) FROM ticket_prices
               WHERE price < (SELECT MAX(price) FROM ticket_prices));

It works, it naturally handles ties as one position, and it does not generalise past about N=3 without becoming unreadable. Worth knowing it exists; not worth writing when window functions are available.

LIMIT ... OFFSET is MySQL and PostgreSQL syntax. SQL Server needs OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY, and it requires an ORDER BY.

WITH monthly AS (
  SELECT DATE_TRUNC('month', enrolled_on) AS month,
         SUM(premium) AS premium
  FROM mutual_fund_sips
  GROUP BY 1
)
SELECT month,
       premium,
       LAG(premium) OVER (ORDER BY month) AS prev_month,
       ROUND(100.0 * (premium - LAG(premium) OVER (ORDER BY month))
             / NULLIF(LAG(premium) OVER (ORDER BY month), 0), 1) AS pct_change
FROM monthly;

The ORDER BY inside OVER is what defines “previous”. It has nothing to do with the ORDER BY of the outer query, and getting them out of sync is a common source of results that look shuffled.

Three things to have ready when this is asked.

The first row returns NULL, always, because there is no earlier row to reach. That is usually correct and should be presented as “no comparison available” rather than patched to zero, which would render as a 100% jump on the chart. If a default genuinely makes sense, LAG takes a third argument: LAG(premium, 1, 0).

NULLIF on the denominator is not decoration. One month with zero collection and the percentage calculation aborts the whole query in PostgreSQL. NULLIF turns the zero into NULL and the expression yields NULL for that row while everything else still computes.

The failure that actually reaches production is the missing month. LAG steps back one row, not one month. If April had no SIP enrolments, April has no row, and May’s LAG reaches all the way back to March while the column header still says “previous month”. The comparison is silently wrong and nothing errors. The fix is a date spine: generate every month, LEFT JOIN the actuals onto it, and let LAG operate over a complete series.

Partitioning applies the same way. PARTITION BY scheme_code ORDER BY month restarts the comparison for each fund, and the first month of every scheme correctly returns NULL rather than borrowing the last month of the previous one.

For a year-on-year comparison, LAG with an offset of 12 works only when every month is present, which is the same reason the spine matters. A self join on a date expression is more robust when the series is sparse.

Start by settling what “duplicate” means. Two grievance tickets from the same citizen about the same complaint type on the same day are probably a double submission. Two tickets a month apart are not. That definition is a business decision and it goes in the PARTITION BY, so ask for it rather than assuming the whole row must match.

Finding them:

SELECT citizen_id, complaint_type, filed_on, COUNT(*) AS copies
FROM grievance_tickets
GROUP BY citizen_id, complaint_type, filed_on
HAVING COUNT(*) > 1;

That gives the duplicated keys and how many copies each has. To see the actual rows, including their IDs, ROW_NUMBER is more useful:

WITH marked AS (
  SELECT ticket_id, citizen_id, complaint_type, filed_on,
         ROW_NUMBER() OVER (PARTITION BY citizen_id, complaint_type, filed_on
                            ORDER BY ticket_id) AS rn
  FROM grievance_tickets
)
SELECT * FROM marked WHERE rn > 1;

Every row with rn > 1 is a surplus copy. Run this SELECT and eyeball the results before you delete anything. Deletes on a production table are not a thing to discover a bug in afterwards.

The delete, in PostgreSQL:

DELETE FROM grievance_tickets
WHERE ticket_id IN (SELECT ticket_id FROM marked WHERE rn > 1);

MySQL 8.0 will not let you delete from a table you are also selecting from in a subquery. The self join form works there:

DELETE t FROM grievance_tickets t
JOIN grievance_tickets keep
  ON keep.citizen_id = t.citizen_id
 AND keep.complaint_type = t.complaint_type
 AND keep.filed_on = t.filed_on
 AND keep.ticket_id < t.ticket_id;

The ORDER BY ticket_id decides which copy survives, and it is a real choice, not boilerplate. Ordering by ID keeps the earliest. ORDER BY last_updated_at DESC keeps the most recently edited, which is usually what you want when the copies are not byte-identical and one of them has been worked on.

Two things that separate a careful answer here. If the table has no unique identifier at all, ROW_NUMBER has nothing to target for deletion, and the standard route is to build a deduplicated copy and swap the tables. And once the data is clean, add a unique constraint on those columns, or the same import will recreate the duplicates next week and you will be running this again.

Both stack one result set on top of another. Only one of them does extra work.

SELECT reg_no, marks FROM exam_results_2025
UNION ALL
SELECT reg_no, marks FROM exam_results_2026;

UNION ALL passes rows straight through. Swap in UNION and the engine must compare every row against every other row to eliminate exact duplicates, which means sorting or hashing the whole combined set. On two ten-million-row result sets that is a substantial amount of work, sometimes spilling to disk, to remove duplicates that in this case cannot exist — a 2025 result and a 2026 result differ in at least the year.

So the rule: use UNION ALL unless you specifically need deduplication and cannot get it more cheaply upstream.

Beyond speed, UNION can also quietly delete correct data. Combine two fee payment feeds where a student legitimately paid ₹5,000 twice on the same date, and if every selected column matches, UNION collapses those into one row. Your total is now ₹5,000 short, with no error and no obvious symptom. UNION ALL keeps both.

The mechanical requirements apply to both. Each branch needs the same number of columns, in the same order, with compatible types. Column names come from the first branch and the rest are ignored, so aliasing the second branch does nothing. A type mismatch is where behaviour diverges by engine: PostgreSQL is strict and will refuse to union text with integer, while MySQL applies its usual coercion and may return something surprising rather than an error.

ORDER BY belongs at the end, applied once to the entire combined result, not to the individual branches. Some engines reject an ORDER BY inside a branch outright; others accept it and ignore it, which is worse because it looks like it worked.

One case where UNION is the right call: merging customer contact lists from two acquired businesses, where the same person genuinely appears in both and you want a single row per person. Deduplication is the actual requirement there, not an accident.

“Monthly active accounts” is not a definition. It is a label with four holes in it.

Entity. An account, a user within an account, or a company with several accounts? A payroll software firm with 40 HR users on one subscription is one active account or forty active users, and both numbers get called MAA in different meetings.

Qualifying action. Logging in, or doing something meaningful like running a payroll cycle? Login-based counts are easy and flatter you, because a user who logs in and immediately leaves is not active in any sense the business cares about.

Window. Calendar month, or rolling thirty days? These diverge every month and neither is wrong.

Inclusions and exclusions. Internal test accounts, accounts in trial, accounts in dunning, accounts belonging to churned customers still inside their notice period.

The written definition then looks like this:

Monthly Active Accounts. Count of distinct billing accounts that completed at least one payroll run in the calendar month. Excludes accounts flagged is_internal. Trial accounts included and reported separately. Source: payroll_runs joined to accounts. Owner: Revenue Operations.

Every clause corresponds to something in the SQL, which is the test of whether a definition is finished.

Two things worth adding unprompted. Name an owner, because a KPI with no owner accumulates undocumented amendments from whoever last edited the dashboard. And check that the source data can actually support the definition; agreeing a definition the warehouse cannot produce wastes everyone’s afternoon, and it is better to say so in the meeting than three days later.

The failure this prevents is specific and common: two dashboards, both labelled MAA, differing by 8%, and nobody able to say which is right because neither has a definition attached. Once that happens, trust in every number on both dashboards drops.

Five checks, in the order that catches the most for the least effort.

Reconcile the total. Compare against something authoritative and independent: the finance ledger, last month’s published pack, the source system’s own dashboard. A gross merchandise figure that does not tie to finance within a known margin is not ready.

Compare with the previous period. A 40% jump in a mature metric is either a real event you should be leading with, or a bug. Either way you need the explanation before someone else asks.

Check the grain and the row count. One row per franchise per month, and 214 franchises means 214 rows. A count of 227 means a join fanned out or a franchise appears under two IDs.

Spot-check by hand. Pick two or three specific franchises and verify their figures directly from the source. This catches the class of error that survives every aggregate check, because the total can be right while the allocation across rows is wrong.

Re-read the filters against the definition. Date boundaries, status exclusions, currency. Confirm the period is inclusive at both ends the way the definition says.

SELECT COUNT(*) AS rows,
       COUNT(DISTINCT franchise_id) AS franchises,
       SUM(net_sales) AS total,
       MIN(month) AS from_month, MAX(month) AS to_month
FROM franchise_monthly_report;

Four lines, and it catches duplication, missing franchises and a wrong date range at once.

Two habits worth mentioning in an interview. Anything that surprises you needs an explanation before it leaves your hands, because “I noticed and here is why” and “I did not notice” are read very differently in a board room. And where a figure rests on an assumption someone might contest, put a one-line footnote on the slide rather than waiting to be asked.

If a number does turn out to be wrong afterwards, correct it quickly, in writing, with the cause and the corrected figure. Quiet fixes are what destroy credibility, not the original mistake.

Two analysts pull the average credit score of loan applicants and get different numbers. One wrote AVG(credit_score). The other wrote SUM(credit_score) / COUNT(*). Both queries are valid, and one of them is answering a different question than the analyst thinks.

SELECT COUNT(*)            AS applications,   -- 10000
       COUNT(credit_score) AS scored,         -- 8200
       SUM(credit_score)   AS total_score,
       AVG(credit_score)   AS mean_score
FROM loan_applications;

So AVG(credit_score) divides by 8,200. The manual version divides by 10,000 and produces a mean roughly 18% lower, because 1,800 thin-file applicants with no bureau history were silently treated as zeros.

Which is correct depends on what NULL means in that column. For a mean credit score, an unknown score should be excluded, and AVG is right. For “average disbursed amount per application” where a NULL means nothing was disbursed, zero is the true value and AVG(COALESCE(disbursed_amount, 0)) is what you want. Decide that before you write the query, not after someone questions the number.

The failure that reaches production most often is the empty set. Filter to a branch that made no disbursals last month and COUNT(*) returns 0, but SUM(disbursed_amount) and AVG(disbursed_amount) both return NULL, not ₹0. The dashboard tile renders blank, and any downstream arithmetic on that NULL propagates NULL through the rest of the calculation. COALESCE(SUM(disbursed_amount), 0) is cheap insurance on every reporting query.

COUNT(DISTINCT credit_score) also skips NULL. And COUNT(1) is identical to COUNT(*), not a NULL question at all, though interviewers sometimes hide it inside this one.

On a straightforward deduplication, these two are interchangeable:

SELECT DISTINCT vehicle_id FROM trips WHERE trip_date >= '2026-08-01';

SELECT vehicle_id FROM trips WHERE trip_date >= '2026-08-01' GROUP BY vehicle_id;

Same rows, same order guarantees (none, in both cases), and on MySQL and PostgreSQL you will typically see the same plan in EXPLAIN. Anyone who tells you one is universally faster is repeating something they read. Check the plan on your own data.

The genuine split is intent. GROUP BY is the right tool the moment you want something about each group: trips per vehicle, total distance, last trip date. DISTINCT is the right tool when you only want the unique values and nothing else, and it signals that intent to whoever reads the query next.

The gotcha that actually costs people: DISTINCT applies to the entire select list, not to the column it happens to sit next to.

SELECT DISTINCT vehicle_id, driver_id FROM trips;

That returns distinct vehicle-driver pairs. A truck driven by four different drivers appears four times, and someone who expected one row per truck now has an inflated fleet count. If you need one row per vehicle plus a representative driver, you need an aggregate or a window function, not DISTINCT.

Related, and worth flagging in a code review: DISTINCT bolted on to a query because the join was returning duplicates. It hides a fan-out rather than fixing it, and it can also delete rows you wanted, if two legitimately different records look identical once the extra columns are dropped from the select list.

WITH base AS (
  SELECT customer_id,
         DATE '2026-08-01' - MAX(purchase_date) AS recency_days,
         COUNT(DISTINCT order_id)               AS frequency,
         SUM(net_amount)                        AS monetary
  FROM nursery_purchases
  WHERE purchase_date >= DATE '2024-08-01' AND purchase_date < DATE '2026-08-01'
  GROUP BY customer_id
),
scored AS (
  SELECT *,
         NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,
         NTILE(5) OVER (ORDER BY frequency)         AS f_score,
         NTILE(5) OVER (ORDER BY monetary)          AS m_score
  FROM base
)
SELECT customer_id, r_score, f_score, m_score,
       CASE WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
            WHEN r_score >= 4 AND f_score <= 2                  THEN 'New or promising'
            WHEN r_score <= 2 AND f_score >= 4                  THEN 'At risk'
            WHEN r_score <= 2 AND f_score <= 2                  THEN 'Lapsed'
            ELSE 'Core' END AS segment
FROM scored;

The ORDER BY recency_days DESC inside the first NTILE is the detail people get wrong. Large recency in days is bad, so descending order puts the most recent buyers in bucket 5 alongside the high scorers on the other two dimensions. Get that backwards and every segment label is inverted, and the query still runs cleanly.

Three things to raise before the interviewer does.

The window matters enormously. A two-year window and a six-month window produce different frequency distributions and different segments for the same customers. Choose it against the purchase cycle: a garden nursery where people buy seasonally needs a longer window than a grocery app.

NTILE forces even quintiles regardless of the underlying shape. If 60% of customers bought exactly once, the frequency quintiles cut arbitrarily through that block, and customers with identical behaviour land in different scores. When ties dominate, use explicit thresholds agreed with the business instead of quintiles, and say why.

Scores are relative to your base. An M-score of 5 means top 20% of your customers, not high value in any absolute sense, and it shifts every time you rerun it.

The segments are only worth building if each one has a different action attached. Champions get early access, at-risk get a win-back offer, lapsed get a cheap reactivation attempt or nothing at all. A segmentation with no action attached to it is a chart, and it will be looked at once.

The current-stage column on the opportunity record tells you where a deal is now. It cannot tell you where it has been, so conversion analysis needs the stage transition log.

WITH stage_entry AS (
  SELECT deal_id, stage, MIN(changed_at) AS entered_at
  FROM deal_stage_history
  GROUP BY deal_id, stage
),
reached AS (
  SELECT stage, COUNT(DISTINCT deal_id) AS deals
  FROM stage_entry GROUP BY stage
)
SELECT stage, deals,
       ROUND(100.0 * deals / LAG(deals) OVER (ORDER BY stage_order), 1) AS conv_from_prev
FROM reached JOIN stage_ref USING (stage)
ORDER BY stage_order;

Velocity comes from the gap between consecutive entries for the same deal, and the median beats the mean here because a handful of deals that sat untouched for eight months will drag an average well past anything typical.

Three issues that make this harder than it looks.

Open deals. A deal currently sitting in Proposal has neither converted nor lost. Counting it as a failure understates the conversion rate, and the distortion is worst for recent cohorts where most deals are still open. Either restrict to deals created before a cut-off long enough for most to resolve, or report open deals as a separate column so the reader can see the denominator.

Skipped and reversed stages. Reps move deals to whichever stage matches the conversation, so a deal can jump from Qualification straight to Negotiation, or move backwards. MIN(changed_at) per stage handles re-entry sensibly, but a skipped stage makes stage-to-stage conversion exceed 100% for the following stage, which is confusing rather than wrong.

CRM hygiene. Stage timestamps reflect when the rep updated the record, not when the event happened, and a rep updating three stages on the last day of the quarter produces zero-day velocity for two of them. Worth flagging to the sales leader, because it is a process finding as much as a data one.

Segmenting by deal size usually matters more than the headline rate. A 22% overall win rate that is 40% for small deals and 9% for enterprise is two different businesses averaged into one misleading number.

SELECT t.region, t.rep_name,
       t.target_units,
       COALESCE(a.actual_units, 0) AS actual_units,
       ROUND(100.0 * COALESCE(a.actual_units, 0) / NULLIF(t.target_units, 0), 1) AS attainment_pct,
       RANK() OVER (PARTITION BY t.region
                    ORDER BY COALESCE(a.actual_units, 0) / NULLIF(t.target_units, 0) DESC) AS rank_in_region
FROM rep_targets t
LEFT JOIN (
  SELECT rep_id, SUM(units_sold) AS actual_units
  FROM equipment_sales
  WHERE sold_on >= DATE '2026-04-01' AND sold_on < DATE '2026-07-01'
  GROUP BY rep_id
) a ON a.rep_id = t.rep_id
WHERE t.quarter = 'FY27Q1';

Driving from the target table with a LEFT JOIN is the structural decision. Start from sales instead and a rep who sold nothing disappears from the report entirely, which is precisely the row a sales director needs to see. The COALESCE turns their absence into a visible zero.

NULLIF on the denominator handles a target of zero, which occurs more often than you expect for new territories and produces a division error that kills the whole query.

Three judgement calls worth raising.

Partial periods. A rep who joined in May measured against a full-quarter target will always look poor. Either prorate the target by days employed, or exclude partial-period reps and say so. Silently ranking them alongside everyone else produces a list that gets argued with rather than acted on.

Ranking scope. Within region compares like with like, since territories differ in size and maturity. Across the company answers a different question and mostly reveals which regions are richer. Ask which one is wanted; if a leaderboard is going on a wall, this decision has consequences.

Ties. RANK gives joint positions, which is what people expect from a leaderboard. ROW_NUMBER would break a genuine tie arbitrarily, and two reps at exactly 104% being ranked 3rd and 4th is a conversation you do not want to have.

One presentation point. Attainment percentage alone rewards low targets. Show target, actual and percentage together so a rep at 130% of a small target is not mistaken for the biggest contributor.

A public library wants members who have never borrowed a book. Three ways to ask for it:

-- 1. LEFT JOIN + IS NULL
SELECT m.member_id
FROM members m
LEFT JOIN loans l ON l.member_id = m.member_id
WHERE l.member_id IS NULL;

-- 2. NOT EXISTS
SELECT m.member_id
FROM members m
WHERE NOT EXISTS (SELECT 1 FROM loans l WHERE l.member_id = m.member_id);

-- 3. NOT IN
SELECT m.member_id
FROM members m
WHERE m.member_id NOT IN (SELECT member_id FROM loans);

The first two always agree. The third agrees only while loans.member_id contains no NULLs. Add one row with a NULL member ID, from a bulk import or a nullable legacy column, and version 3 returns an empty set. Not an error, not a warning, just zero members. Because of how NULL comparisons resolve, the expression can never be true once a NULL is in the list.

Ship NOT EXISTS. It is immune to that, it stops scanning the moment it finds the first match, and it states the intent in the query text rather than encoding it as a side effect of a NULL check.

The LEFT JOIN form has a detail that catches people in review: the IS NULL test must be on a column that is never NULL in a matched row. Test the join key or the right table’s primary key. If you write WHERE l.returned_on IS NULL instead, you have quietly changed the question to “members with an outstanding loan”, which is a different report entirely.

On scale, the LEFT JOIN form materialises every matching pair before the filter throws them away. A member with 400 loans generates 400 rows so that 399 of them can be discarded. NOT EXISTS never builds those rows. On small tables the optimiser often converts one form into the other and the plans converge, but on a wide loans table the difference is measurable, and it goes the same way on both MySQL and PostgreSQL.

The two sides do not match, and that is the entire difficulty. Budget exists for departments that spent nothing. Actuals exist for cost centres nobody budgeted. An inner join hides both, which are the two things a finance controller most wants to see.

SELECT COALESCE(b.dept_code, a.dept_code)       AS dept_code,
       COALESCE(b.period, a.period)             AS period,
       COALESCE(b.budget_amount, 0)             AS budget,
       COALESCE(a.actual_amount, 0)             AS actual,
       COALESCE(a.actual_amount, 0) - COALESCE(b.budget_amount, 0) AS variance,
       ROUND(100.0 * (COALESCE(a.actual_amount, 0) - COALESCE(b.budget_amount, 0))
             / NULLIF(b.budget_amount, 0), 1)   AS variance_pct,
       CASE WHEN b.dept_code IS NULL THEN 'unbudgeted spend'
            WHEN a.dept_code IS NULL THEN 'no spend against budget' END AS flag
FROM dept_budget b
FULL OUTER JOIN dept_actuals a
  ON a.dept_code = b.dept_code AND a.period = b.period;

MySQL has no FULL OUTER JOIN, so union a LEFT and a RIGHT join there.

The sign convention has to be settled before you write the CASE statements, and it is not obvious. Actual minus budget is positive when spending exceeds budget, which is unfavourable for a cost line and favourable for a revenue line. Reporting both in one table with one formula means half the rows read backwards. Either split cost and revenue lines, or add an explicit favourable-unfavourable column computed per line type. Ask finance which convention their existing pack uses and match it.

Grain is the other decision. Budgets are frequently set annually and spread evenly across twelve months, while actuals land unevenly. Comparing a single month against one twelfth of the year makes any department with lumpy spending look wildly off. Year-to-date comparison smooths this and is usually the more meaningful view; if the business wants monthly, note that the phasing is artificial.

One more thing worth flagging in the output. A variance percentage against a budget of zero is undefined, and NULLIF keeps the query alive, but a department spending ₹4 lakh against a zero budget is the most interesting row in the report. Make sure it is visible rather than blank.

Nothing special happens in the engine. The table appears twice in the FROM clause, and the aliases are what make it readable:

SELECT e.staff_id,
       e.full_name  AS employee,
       m.full_name  AS reports_to
FROM store_staff e
LEFT JOIN store_staff m ON m.staff_id = e.manager_id;

e is the employee side, m is the manager side. Same physical table, two independent row sources.

The choice of LEFT over INNER is the whole interview question. A retail chain’s regional head has manager_id set to NULL. With an INNER JOIN that row fails to match and disappears, so your org chart is missing exactly the person at the top, and the row count is one short in a way nobody notices. LEFT JOIN keeps them with reports_to as NULL, and COALESCE(m.full_name, 'Regional Head') presents it cleanly.

Ask the interviewer one thing before writing: is manager_id guaranteed to point at a live row? Real HR tables accumulate orphans when a manager leaves and their record is soft-deleted or archived. Those employees also come back with a NULL manager under a LEFT JOIN, and they mean something completely different from the person at the top. Distinguishing the two needs an explicit check on the manager row’s status, not just a NULL test.

Self joins are not only for hierarchies. Comparing a row to its neighbour uses the same shape, such as finding two stock transfers between the same pair of stores on the same day:

SELECT a.transfer_id, b.transfer_id
FROM transfers a
JOIN transfers b
  ON b.from_store = a.from_store
 AND b.transfer_date = a.transfer_date
 AND b.transfer_id > a.transfer_id;

That > on the key is doing real work. Without it every row pairs with itself, and every genuine pair appears twice in reversed order. On a large table, forgetting it turns a modest result into something the query planner spends a very long time producing.

One honest caveat for the follow-up: a single self join gives you one level. Arbitrary depth needs a recursive CTE, available in MySQL 8.0 and PostgreSQL, not in MySQL 5.7.

Working days is the phrase that changes the query. Seven working days is not submitted_at + INTERVAL '7 days', and interval arithmetic cannot express it, because weekends and state holidays are not derivable from a date. You need a calendar table with a working-day flag and a running day number:

SELECT a.application_id,
       c1.working_day_seq AS start_seq,
       c2.working_day_seq AS end_seq,
       c2.working_day_seq - c1.working_day_seq AS working_days_taken
FROM passport_applications a
JOIN work_calendar c1 ON c1.cal_date = a.submitted_on
JOIN work_calendar c2 ON c2.cal_date = a.dispatched_on;

Subtracting sequence numbers gives working days directly, and a holiday added to the calendar corrects every historical figure at once.

Now the denominator, which is where the number gets quietly flattered. Restricting to dispatched applications computes compliance over completed work only. The applications stuck in verification for three weeks are invisible, and they are exactly the failures the charter exists to prevent. A case already past seven working days has a known outcome even without a dispatch date, so classify it:

CASE WHEN dispatched_on IS NOT NULL AND working_days_taken <= 7 THEN 'met'
     WHEN dispatched_on IS NULL AND days_elapsed_so_far <= 7    THEN 'in progress'
     ELSE 'breached' END

Three states, nothing hidden, and the in-progress bucket size tells the reader how provisional the recent months are.

Ask about clock pauses before writing anything. Most government and helpdesk SLAs stop counting while awaiting something from the applicant, which means elapsed time is a sum of active spells drawn from a status history table, not a subtraction between two columns. Compute it the simple way when the policy says calendar time and you will be defending a wrong number in front of the centre head.

One thing to add to the output. A single percentage gives a manager nowhere to go. Break it by centre and by application category, and show the 90th percentile alongside the mean, because a centre at 94% where the failures take a month has a different problem from one at 88% where they take nine days.

Everything the WHERE clause let through collapses into one group. One group, one row:

SELECT COUNT(*)          AS bills_issued,
       SUM(units_billed) AS total_units,
       MAX(amount_due)   AS highest_bill
FROM electricity_bills
WHERE billing_month = '2026-07';

One row back, always. Not one row per consumer, not one per division.

The interesting case is when the filter matches nothing. Point that query at a division with no billing run and you still get one row, containing 0 for the count and NULL for the sum and the max. This catches people writing validation logic. A check like IF (SELECT SUM(units_billed) ...) > 0 does not go to the false branch on empty data. It goes to unknown, which is not true, and depending on the surrounding code that can look like a pass. Wrap it in COALESCE and compare a real number.

The other half of the question is what happens when you add a plain column:

SELECT consumer_id, MAX(amount_due) FROM electricity_bills;

There is one group covering millions of rows, and one consumer_id has to be chosen out of all of them. PostgreSQL and SQL Server refuse to guess and raise an error. MySQL with ONLY_FULL_GROUP_BY disabled runs it and returns some consumer_id, not necessarily the one holding the maximum bill. That is the actual danger: the output looks exactly like the answer to “who had the highest bill”, it is formatted like the answer, and it is frequently not the answer. Getting the row that holds the maximum needs a window function, an ORDER BY with LIMIT, or a subquery on the max value.

Because there is a group, HAVING is legal here too. SELECT SUM(units_billed) FROM electricity_bills HAVING SUM(units_billed) > 100000 gives you either one row or an empty result, which is a compact way to write a threshold check.

EXTRACT(YEAR FROM invoice_date) returns 2026 for both February 2026 and June 2026, which fall in different financial years under an April cycle. Every year-on-year comparison built that way straddles the boundary, and the failure is invisible because the totals still look plausible.

The inline fix works and does not scale:

SELECT CASE WHEN EXTRACT(MONTH FROM dispatch_date) >= 4
            THEN EXTRACT(YEAR FROM dispatch_date)
            ELSE EXTRACT(YEAR FROM dispatch_date) - 1 END AS fy_start,
       SUM(bags_dispatched)
FROM cement_dealer_dispatches
GROUP BY 1;

Copy that into thirty reports and one of them will carry a different off-by-one, which is how two dashboards start disagreeing about Q4 while both look correct in isolation.

The maintainable answer is a calendar table with one row per date and columns for fiscal year, fiscal quarter, fiscal month number and a display label. Join to it and the rule lives in one place, it is indexable, and a change in convention becomes a data update rather than thirty code changes. It also holds what no date function can derive: dealer holidays, working-day flags and retail period structures.

Label the year consistently as FY2026-27 and store an integer sort key beside it. FY26 is ambiguous about which end it names, and finance and sales will reliably choose opposite readings. Text labels also sort alphabetically, which happens to work until a format changes.

Two further traps worth naming. Retail and FMCG businesses often run 4-4-5 or 4-5-4 periods, where a fiscal month is a fixed count of weeks and never aligns with a calendar month. That is not computable from a date and must come from the table. And a 53-week year appears periodically, so year-on-year comparisons should run week against week rather than date against date in those businesses.

What the interviewer is really checking is whether you ask about the fiscal convention during requirements. Discovering it after the variance meeting is the expensive way to learn it.

The instinct is to hand over something already shaped like the finished table. Resist it, because a pivot table needs raw material and pre-pivoted output has to be unpicked before it can be used.

SELECT branch_name,
       service_type,
       stylist_name,
       DATE_TRUNC('month', appointment_on)::date AS month,
       COUNT(*)                                  AS appointments,
       SUM(bill_amount)                          AS revenue
FROM salon_appointments
GROUP BY 1, 2, 3, 4
ORDER BY 1, 2, 3, 4;

One row per branch, service, stylist and month, with every dimension as its own column. The user drags whatever they need onto rows and columns. Hand over a wide table with twelve month columns and they get exactly the one view you happened to anticipate.

Four rules for an export like this.

Keep the types. Wrapping revenue in TO_CHAR produces text that Excel will not sum, and the user will not know why. Formatting belongs in the spreadsheet.

Split the keys. 'Indiranagar - Hair Colour' in one column cannot be filtered by branch; two columns can, and can be concatenated later if wanted.

Name columns for humans, since the header row becomes the pivot field list and those names persist.

Know what Excel changes on open. Long numeric identifiers lose leading zeros or flip to scientific notation, and anything that resembles a date is converted. Where a code is genuinely a code, note that the file should be imported rather than double-clicked.

Leave out the totals row. Pivot tables compute their own subtotals, and a stray total sitting in the source gets aggregated with the detail, doubling every figure. It is a common error and it comes from trying to be helpful.

For a BI tool the guidance is the same and firmer. Supply raw counts and sums rather than precomputed percentages, so the tool can recompute correctly at whatever level the user drills into.

The damage is not the new definition. It is a chart where the definition changes at some unmarked point, so a 6% drop is read as a business problem when it is a measurement change.

Start by quantifying it. Run both definitions over the same three months:

SELECT month,
       COUNT(DISTINCT CASE WHEN status = 'COMPLETED' THEN job_id END)      AS old_def,
       COUNT(DISTINCT CASE WHEN status = 'COMPLETED'
                            AND customer_rating IS NOT NULL THEN job_id END) AS new_def
FROM home_service_jobs
WHERE month >= '2026-04-01'
GROUP BY month ORDER BY month;

Now you can tell leadership the change moves the metric by 6.2%, which converts an argument into a decision.

Then pick one of two approaches, and say which.

Restate history. Recompute every prior period under the new definition. The series stays comparable end to end, which is what you want for trend analysis. It requires that the historical data still supports the new logic, and it means published numbers change, so anyone holding an old board pack sees different figures.

Break the series. Keep old periods as they were, start the new definition at a stated date, and mark the break on every chart. Honest and simple, at the cost of losing clean comparability across the boundary.

For a quarterly board metric, restating with both series shown for the overlap is usually the most defensible: the audience sees the old number they remember, the new number, and the gap explained once.

What is never acceptable is applying the new definition going forward while leaving old periods untouched in the same unmarked series.

Two things to do regardless. Version the definition with an effective date, the reason, and who approved it, so someone in a year can reconstruct which rule produced which period. And check reproducibility before promising a restatement: if the old definition depended on a field that has since been overwritten, history cannot be recomputed and breaking the series is the only honest option.

An ad-hoc pull is a conversation. Someone asks, you answer in twenty minutes, the query is read once and discarded. Hardcoding a date range is fine. Skipping comments is fine. Nobody will ever run it again.

A recurring report is a small piece of software with a two-year life, and everything about it changes.

Dates must be relative to the run, not typed in:

WHERE dispatched_on >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
  AND dispatched_on <  DATE_TRUNC('month', CURRENT_DATE)

Categories cannot be assumed fixed. WHERE channel IN ('retail','online','distributor') silently drops the channel that gets added in November, and the total quietly stops matching. Either enumerate exhaustively and check, or leave the filter open and let unknown values appear.

Empty groups need a spine. A depot that made no dispatches this month vanishes from a GROUP BY, so the report has fewer rows than depots and someone assumes the depot was excluded.

Comments earn their place here in a way they do not in ad-hoc work. Not what the SQL does, but why the odd bits are there: why a status is excluded, why one depot is remapped, which conversation produced the threshold.

Add a sanity check that runs alongside it. Row count and total compared with recent weeks, so a week that returns nothing announces itself instead of arriving as a blank slide.

The judgement worth voicing: the third time the same ad-hoc request arrives, it is a recurring report and should be rewritten as one. Analysts lose enormous amounts of time re-deriving something they have already built twice, each version slightly different from the last, which is also how two versions of the same number start circulating.

Do not say “that’s hard”. Say what the work is and what it buys.

The concrete version: matching two years of clinic appointment records against the insurer’s claim feed requires manual reconciliation of about 4,000 records where the patient identifier does not match cleanly, because the two systems were merged in March and identifiers diverge. That is roughly two days. A sample-based estimate is available this afternoon with an error range of about two percentage points.

Then ask the question that resolves it: what decision does the number drive? If the board is choosing between renewing a contract and not, and both 8% and 8.4% lead to renewal, the two days buy nothing. If the figure goes into a regulatory filing or an invoice to the insurer, exact is the only acceptable answer and the two days are not negotiable.

Offering a middle path usually lands better than either extreme. An estimate today with the caveat attached, and the exact figure by Thursday if the estimate turns out to be near the decision boundary. That respects the deadline without pretending precision you do not have.

Two things about presentation. Label an estimate as an estimate everywhere it appears, with the method and the range, because an approximate number stripped of its caveat travels through three slide decks and arrives at the board as fact. And give the range rather than a single figure: “between 7.8% and 9.1%, most likely around 8.4%” is harder to misuse than “8.4%”.

If they insist on exact after hearing the trade-off, do it. It is their call, and you have made the cost visible, which was your job. Push back once with information, not twice with resistance.

The last part of the answer is what changes afterwards. If this reconciliation will be needed repeatedly, the two days spent building a persistent identifier mapping is worth more than the two days spent producing one number, and that is the proposal to raise while the pain is fresh.

Anyone can read the query. Nobody can read why the query excludes status 7.

Six things to write down, in rough order of value.

Grain. One row per exhibitor per event. This is the first thing a reader needs and the thing most often missing.

Column meanings and units. booth_revenue is net of agency commission, in rupees, excluding GST. Ambiguity here produces two people confidently comparing different quantities.

Filters and their reasons. Cancelled bookings excluded because they are retained in the source for audit. Internal stands excluded per the definition agreed with events operations in May.

Sources and freshness. Which tables, and how current they are. A dashboard built on a table that refreshes weekly should not be read as live, and the reader has no way to know unless told.

Known limitations. Data before April 2024 predates the CRM migration and undercounts regional events. Sponsorship revenue is not included. Limitations are the most valuable section and the one people skip, because they are what stop the number being used for something it cannot support.

Owner and last review date. So there is someone to ask.

Two practical points that separate documentation that works from documentation that exists.

Keep it next to the query, not in a separate wiki that drifts. A comment block at the top of the SQL file, or the description field in your BI tool or dbt model, is read by the person who needs it at the moment they need it.

Document the source tables you do not control by writing down what you have observed rather than what you assume: this column is null for records before 2024, this status code appears undocumented in about 2% of rows. Nobody else has written that down and it is exactly what the next analyst will lose a day rediscovering.

A coworking operator signs a twelve-month desk agreement worth ₹6 lakh on 1 March, invoices quarterly, and provides the desk every day for a year.

Three numbers, one contract, no error anywhere. The argument in the meeting is not about data quality, it is about two teams using the same word for different quantities.

Recognition needs a schedule that spreads contract value across the service term:

WITH spread AS (
  SELECT m.agreement_id,
         d.month,
         m.contract_value / (m.term_months * 1.0) AS monthly_revenue
  FROM desk_agreements m
  JOIN month_calendar d
    ON d.month >= DATE_TRUNC('month', m.starts_on)
   AND d.month <  DATE_TRUNC('month', m.starts_on) + (m.term_months || ' months')::INTERVAL
  WHERE m.status <> 'CANCELLED_BEFORE_START'
)
SELECT month, SUM(monthly_revenue) AS recognised_revenue
FROM spread GROUP BY month ORDER BY month;

Straight-line spreading fits a service delivered evenly. It does not fit everything: a one-off fit-out charge is earned when the work is done, and a meeting-room package billed by usage is earned as consumed. Ask which model applies per revenue line rather than applying one rule to the whole contract table.

Two things that will come up from finance.

Deferred revenue is the reconciling item between billings and recognition, and it is the figure the auditors look at. Anything you build should tie to the deferred balance on the ledger, or you will be asked to explain a gap you did not know existed.

Early cancellation breaks a schedule generated upfront. The remaining months were already spread and now must not be recognised. Either regenerate the schedule on each run from current contract state, which is the cleaner approach, or carry a cancellation date into the spreading condition.

For forecasting cash, billings is the closer proxy and recognised revenue is not, since recognition is deliberately decoupled from when money moves. Name which one a chart shows, every time.

WITH pairs AS (
  SELECT a.item_id AS i1, b.item_id AS i2, COUNT(DISTINCT a.bill_no) AS both_ct
  FROM bakery_bill_lines a
  JOIN bakery_bill_lines b
    ON b.bill_no = a.bill_no
   AND b.item_id > a.item_id
  WHERE a.bill_date >= DATE '2026-05-01'
  GROUP BY a.item_id, b.item_id
),
singles AS (
  SELECT item_id, COUNT(DISTINCT bill_no) AS item_ct
  FROM bakery_bill_lines WHERE bill_date >= DATE '2026-05-01' GROUP BY item_id
),
total AS (
  SELECT COUNT(DISTINCT bill_no) AS n FROM bakery_bill_lines WHERE bill_date >= DATE '2026-05-01'
)
SELECT p.i1, p.i2, p.both_ct,
       ROUND((p.both_ct * 1.0 / t.n) /
             ((s1.item_ct * 1.0 / t.n) * (s2.item_ct * 1.0 / t.n)), 2) AS lift,
       ROUND(100.0 * p.both_ct / s1.item_ct, 1) AS conf_i1_to_i2
FROM pairs p
JOIN singles s1 ON s1.item_id = p.i1
JOIN singles s2 ON s2.item_id = p.i2
CROSS JOIN total t
WHERE p.both_ct >= 150
ORDER BY lift DESC;

The b.item_id > a.item_id predicate stops an item pairing with itself and returns each unordered pair once rather than twice.

Lift is what turns co-occurrence into a finding. Bread appears in 55% of bills and tea powder in 30%, so they will land together in a great many bills by arithmetic alone, and a lift near 1 says the pairing carries no information. A lift of 5 on a moderate count says something is actually happening. Confidence adds direction: buyers of item A who also take B is not the same rate as the reverse, and for a bundle or a shelf decision the direction matters.

The support floor is not decoration. A pair in nine bills can show a lift of 30 and be pure noise, because a tiny denominator makes the ratio unstable. Set the minimum from your own volume rather than borrowing a threshold.

Cost is the practical limit. The self join is quadratic in lines per bill, so a bulk order with 40 lines contributes 780 pairs by itself. Restrict the period, cap to the top few hundred items by volume, or roll up to category first. Triples multiply again and are rarely worth writing in SQL.

Two cautions before anyone acts on the output. Promotions and adjacent shelf placement manufacture strong pairs that vanish when the promotion ends, so check what was running. And a pair the counter staff could have named without any query is not an insight, however high the lift.

WITH touches AS (
  SELECT t.enquiry_id, t.channel, t.touched_at,
         ROW_NUMBER() OVER (PARTITION BY t.enquiry_id ORDER BY t.touched_at)      AS first_rn,
         ROW_NUMBER() OVER (PARTITION BY t.enquiry_id ORDER BY t.touched_at DESC) AS last_rn
  FROM solar_touchpoints t
  JOIN solar_installations i ON i.enquiry_id = t.enquiry_id
  WHERE t.touched_at <= i.contract_signed_at
    AND t.touched_at >= i.contract_signed_at - INTERVAL '90 days'
)
SELECT channel,
       COUNT(CASE WHEN first_rn = 1 THEN 1 END) AS first_touch_wins,
       COUNT(CASE WHEN last_rn  = 1 THEN 1 END) AS last_touch_wins
FROM touches
GROUP BY channel
ORDER BY last_touch_wins DESC;

Two numberings over the same window produce both models in one pass, which is how they should be presented, because the disagreement between the columns carries more information than either column alone.

The touched_at <= contract_signed_at filter is easy to leave out and quietly corrupting. Post-sale communication such as an installation scheduling SMS would otherwise become the last touch, handing conversion credit to an operational channel that sold nothing.

Reading the two columns together is the skill being tested. A channel strong on first-touch and weak on last-touch is generating awareness for a product with a long consideration cycle; a household researching rooftop solar takes weeks and converts through a site visit or a branded search. Cut that channel’s budget on last-touch evidence and the enquiry pipeline thins out two quarters later, by which point nobody connects the two events.

The lookback window is a business decision that materially moves the answer. Solar is a high-consideration purchase, so 90 days is more defensible than 30, and widening it shifts credit toward awareness channels while narrowing it concentrates credit at the bottom. State the window on the output so a reader can see the assumption rather than inherit it.

Two things worth volunteering. Neither model is correct: they are conventions for dividing credit that no dataset can settle, and multi-touch or time-decay approaches are simply different conventions with the same limitation. And enquiries with no recorded touchpoint, from a neighbour’s referral or a hoarding, belong in an explicit unattributed bucket. That bucket is frequently the largest one, and hiding it makes every percentage in the table wrong.

Sixty lakh cases is not a result on its own. The result is what happened to total category volume in the same territories.

SELECT CASE WHEN sale_month < DATE '2025-09-01' THEN 'pre' ELSE 'post' END AS period,
       SUM(CASE WHEN sku_code = 'COLA-ZERO' THEN cases ELSE 0 END)     AS new_variant,
       SUM(CASE WHEN sku_code = 'COLA-REG'  THEN cases ELSE 0 END)     AS original,
       SUM(cases)                                                      AS category_cases
FROM bottler_dispatches
WHERE sale_month >= DATE '2024-09-01' AND sale_month < DATE '2026-09-01'
  AND brand_family = 'cola'
GROUP BY 1;

Three patterns come out of that comparison. Category up by roughly the variant’s volume with the original flat means genuinely incremental demand. Category flat with the original down by about what the variant gained means substitution, and the launch has rearranged the portfolio without adding a case. Category up by less than the variant’s volume means partial cannibalisation, which is the usual real-world answer, and the incremental portion is the number worth quoting.

Seasonality and market trend are the objections that will land, correctly. Cola volumes swing hard with summer and with the festival calendar, so a pre-versus-post cut confounds the launch with the season. Two defences. Compare the same months a year apart, so the seasonal pattern sits on both sides. And use a control: territories or channels where the variant was not distributed, and check whether the original declined there too. If it declined equally in the control, the variant is not the cause and something broader is happening to the category.

Margin can invert the verdict entirely. If the zero-sugar variant carries a better contribution per case, customers switching to it is a good outcome even at flat volume. Compute category contribution, not just case count, before anyone calls the launch a failure.

Outlet-level data makes the analysis considerably stronger, and it is worth asking for. Take outlets that stocked the original before launch and track what they ordered after: switched entirely, stocked both, or reduced total order size. Aggregate dispatch data can only show that a total moved; outlet-level movement shows who moved and whether the variant opened any new accounts.

SELECT CASE WHEN discount_pct = 0      THEN 'full price'
            WHEN discount_pct <= 15    THEN 'up to 15%'
            WHEN discount_pct <= 30    THEN '16-30%'
            ELSE 'over 30%' END                       AS band,
       COUNT(*)                                       AS orders,
       SUM(net_amount)                                AS revenue,
       SUM(net_amount - landed_cost)                  AS gross_margin,
       ROUND(100.0 * SUM(net_amount - landed_cost)
             / NULLIF(SUM(net_amount), 0), 1)         AS margin_pct,
       ROUND(AVG(units_per_order), 2)                 AS avg_units
FROM footwear_orders
WHERE ordered_on >= DATE '2026-06-01' AND ordered_on < DATE '2026-08-01'
GROUP BY 1 ORDER BY 1;

The arithmetic that settles the question is worth being able to do out loud. On a 40% gross margin, a 30% discount does not remove 30% of profit; it removes three quarters of it, because the entire discount comes out of margin. Selling 18% more pairs at that depth leaves you well behind. Being able to walk an interviewer through that in the room matters more than the query text.

Incrementality is the harder half and aggregate sales data cannot resolve it. Every discounted order that would have happened anyway is margin given away for nothing. What you can do is compare against stores or a period without the promotion, and check whether the discounted orders came from new customers or from regulars who buy every season regardless.

Two things to rule out before concluding.

Product mix. Overall margin percentage can fall because the sale cleared low-margin winter stock while full-margin new season lines were flat. That is a mix shift, not a discount effect, and it needs the figures broken out by category before anyone draws a conclusion.

Baseline. Compare against the same months last year rather than only the preceding quarter, or a normal seasonal uplift gets credited to the campaign.

One longer-run point worth raising even though the query does not show it: repeated discounting trains customers to wait, so full-price demand erodes and each campaign has to go deeper for the same lift. That shows up as a rising share of orders in the discounted bands quarter after quarter, and it is a query worth running alongside this one.

Any sizing estimate is a chain: eligible units, times adoption rate, times price, times frequency. The exercise is worth doing only because it forces you to say which links are measured and which are guessed.

Take a plan to sell managed cafeteria contracts to corporate offices.

SELECT city,
       COUNT(DISTINCT client_id)                                        AS clients,
       SUM(seats_served)                                                AS seats,
       ROUND(AVG(annual_contract_value), 0)                             AS avg_acv,
       ROUND(AVG(annual_contract_value) / NULLIF(AVG(seats_served), 0), 0) AS acv_per_seat
FROM cafeteria_contracts
WHERE status = 'ACTIVE' AND city IN ('Bengaluru','Hyderabad','Pune','Gurugram')
GROUP BY city;

Two links now come from behaviour rather than assumption: contract value per seat, and the spread of that figure across cities. A revenue-per-seat number derived from live contracts is far harder to argue with than an assumed price point.

The chain reads: office seats in target cities, times a realistic share you could win, times revenue per seat. Write it as a labelled list where every line is marked measured or assumed, so a reviewer attacks the weak link directly instead of arguing about the total.

Two disciplines make it credible.

Give a range. Win share at 3%, 6% and 10% produces three numbers, and showing all three is both more honest and more useful than one point estimate. Nobody believes ₹387.4 crore, and the decimal actively reduces trust.

Check against something independent. If your bottom-up figure implies more cafeteria seats than there are office workers in those cities, an assumption is wrong by an order of magnitude, and only an external comparison will catch it.

Extrapolating four metros nationally needs an explicit scaling basis, such as organised-sector office stock, plus the assumption that other cities behave like these four. They usually do not, since metro penetration and contract values both run higher, so say the estimate is likely optimistic rather than presenting it as neutral.

If your number comes in at a third of the slide, that is the finding, not an embarrassment. Take the chain back and ask which link they assumed differently. Nine times out of ten it is the win share, and that is a productive conversation to have before the budget is committed.

SELECT DATE_TRUNC('month', invoice_date) AS month,
       SUM(i.amount * r.rate_to_inr)     AS revenue_inr
FROM garment_export_invoices i
JOIN fx_rates r
  ON r.currency = i.currency
 AND r.rate_date = i.invoice_date
GROUP BY 1 ORDER BY 1;

An inner join here is deliberate. The tempting alternative is a LEFT JOIN with COALESCE(rate, 1), and it is dangerous: a missing rate silently converts a dollar invoice at 1:1 and understates revenue by a factor of eighty, with no error and a total that looks merely disappointing. An inner join loses the row instead, the count drops, and you investigate. Better still, run an explicit check for unmatched invoices before publishing.

Missing rates are normal, not exceptional. Currency markets close at weekends and on holidays, so any invoice dated a Saturday has no rate on that date. The standard handling is to carry forward the last published rate, which needs a lateral join or a window over the rate table rather than an equality match on the date. Ask finance whether their policy is previous close, monthly average, or a fixed budget rate.

Those three policies are genuinely different tools. Transaction-date rates give economic accuracy and keep historical figures stable once loaded. A monthly average smooths daily noise and is common in management reporting. A budget rate fixed for the year strips FX movement out entirely so operational performance is visible, and it is deliberately not the statutory number.

The failure finance will spot immediately is a report that converts everything at the current rate. Rerun it in December and last April’s revenue has changed. To them that is a broken report, and they are right.

Offer the FX split before being asked. Compute the same period at both actual and constant rates, and the difference is the currency contribution. For an export house, the board will ask that question the moment the rupee moves sharply, and having the number ready is what distinguishes an analyst who reports from one who anticipates.

SELECT d.month_end,
       COUNT(DISTINCT s.agent_id) AS headcount
FROM month_end_dates d
JOIN callcentre_agent_spells s
  ON s.joined_on <= d.month_end
 AND (s.exited_on IS NULL OR s.exited_on > d.month_end)
WHERE d.month_end BETWEEN DATE '2025-04-30' AND DATE '2026-03-31'
GROUP BY d.month_end
ORDER BY d.month_end;

The join condition is the entire answer: present on the snapshot date means joined by then and not yet gone. Every month-end recounts the whole population from scratch, so the series cannot drift.

The cumulative alternative, opening balance plus joiners minus leavers, looks more efficient and is fragile. One missing or misdated record anywhere in the history flows into every subsequent month, and by the time the closing figure is questioned there is no way to identify which month introduced it. The recount approach corrects itself the moment the underlying data is fixed.

> versus >= on the exit date is a genuine decision. An agent whose last working day is 31 March: in the March headcount or not? Most HR conventions treat the final working day as present, which exited_on > month_end gives. Write the convention down, because a one-person difference in a board figure generates a startling volume of email.

Rehires are where this gets interesting, and a call centre has plenty. If the source keeps one row per person and a rejoin overwrites the earlier record, the gap disappears and the person appears continuously employed, which is wrong. If it keeps one row per employment spell, the query above handles it correctly, and the COUNT(DISTINCT agent_id) matters: without it, anyone with two overlapping spells from a transfer recorded as a rejoin gets counted twice. Ask which shape the source uses before writing anything.

Two refinements to have ready. Agents serving notice are usually inside headcount and outside an active-and-deployable figure, so those are two metrics with different definitions and both will be requested. And average headcount for the month, which operations uses for cost per seat, is the mean of daily counts rather than the average of opening and closing, and the two diverge sharply in a business with heavy mid-month churn.