SQL Query Fundamentals Questions

Filtering to completed orders belongs in WHERE (a per-row condition, evaluated before aggregation); requiring at least 100 such orders per vendor belongs in HAVING (a per-group condition on the aggregate count), and mixing them up produces a subtly wrong number rather than an error.

Structured elaboration

SELECT v.vendor_id, v.name, AVG(o.amount) AS avg_order_value, COUNT(*) AS n
FROM orders o
JOIN vendors v ON v.vendor_id = o.vendor_id
WHERE o.status = 'completed'
GROUP BY v.vendor_id, v.name
HAVING COUNT(*) >= 100;

Putting the status filter in HAVING instead of WHERE would still produce a result, but the AVG and COUNT would be computed over ALL orders (completed and not), then filtered by a HAVING condition that's checking the wrong thing entirely; the two clauses aren't interchangeable substitutes even though both look like "filtering". This exact WHERE-then-HAVING combination (filter raw rows, then threshold the aggregate) is one of the most common realistic SQL patterns in business reporting: exclude bad/irrelevant rows first, then only surface groups with enough volume to be statistically meaningful.

Worked example

Given vendor 1 with orders of 100 (completed), 200 (completed), and 50 (refunded), and vendor 2 with a single 300 (completed) order: filtering to completed orders first, vendor 1's average is (100+200)/2 = 150 across 2 qualifying orders; vendor 2 has only 1 completed order. If the HAVING threshold were COUNT(*) >= 2, vendor 1 passes (2 completed orders) and vendor 2 is filtered out (only 1). The same pattern generalizes directly to the "at least 100 orders" real threshold in the question, just with a larger dataset.

Trade-offs and pitfalls

The same skill applies to several close variants of this pattern: minimum-order-count thresholds, minimum-transaction-count thresholds, and duplicate-detection HAVING counts are all the identical WHERE-then-HAVING shape with a different business label attached. Recognizing the shape matters more than memorizing any one instance of it.

Applying De Morgan's laws, NOT (status = 'shipped' OR status = 'delivered') becomes status != 'shipped' AND status != 'delivered' (or, more robustly with NULLs in mind, status NOT IN ('shipped', 'delivered')).

Structured elaboration

De Morgan's laws state: NOT (A OR B) = (NOT A) AND (NOT B), and symmetrically NOT (A AND B) = (NOT A) OR (NOT B). Applied here: NOT (status = 'shipped' OR status = 'delivered') distributes the NOT across the OR, flipping it to AND, and negates each comparison: status != 'shipped' AND status != 'delivered'.

Worked example

For a row with status = 'shipped': the original form is NOT (TRUE OR FALSE) = NOT TRUE = FALSE. The rewritten form is FALSE AND TRUE = FALSE. Both agree the row is excluded, as expected since it IS shipped.

For a row with status = 'processing' (neither shipped nor delivered): both status != 'shipped' AND status != 'delivered' and status NOT IN ('shipped', 'delivered') correctly evaluate to TRUE, including the row. For a row with status = NULL: both forms evaluate to UNKNOWN (NULL != 'shipped' is UNKNOWN, not TRUE), so the row is silently excluded, even though it isn't actually shipped or delivered either. The NULL-inclusive rewrite, (status != 'shipped' AND status != 'delivered') OR status IS NULL, correctly includes both the 'processing' row and the NULL row, while still excluding 'shipped' and 'delivered'.

Trade-offs and pitfalls

Removing a top-level NOT often makes a predicate easier for a human to scan (double negatives are hard to reason about quickly) and, on some optimizers, easier to reason about for index usage, since a bare AND of positive-form comparisons composes more predictably with other WHERE clauses than a NOT wrapped around a compound expression. The rewrite is purely logical, though: if status can be NULL, both the original and the rewritten form silently exclude those rows under three-valued logic, since NULL != 'shipped' evaluates to UNKNOWN, not TRUE, and a WHERE clause only keeps rows where the predicate is TRUE. To include NULLs explicitly, OR in an IS NULL check: (status != 'shipped' AND status != 'delivered') OR status IS NULL (or the NOT IN equivalent, status NOT IN ('shipped', 'delivered') OR status IS NULL).

Conditional aggregation lets one query produce several columns, each summing only the rows matching a specific condition, instead of running separate queries per category and stitching the results together.

Structured elaboration

SELECT strftime('%Y-%m', order_date) AS month,
  SUM(CASE WHEN channel = 'online' THEN total_amount ELSE 0 END) AS online_revenue,
  SUM(CASE WHEN channel = 'in_store' THEN total_amount ELSE 0 END) AS in_store_revenue
FROM orders
GROUP BY month;

Each SUM has its own CASE expression that zeroes out rows not matching that column's condition, so every row contributes to exactly one of the conditional sums (or none, if some third channel value exists and isn't covered) while all rows still participate in the single GROUP BY pass. This is meaningfully more efficient than running two separate queries (one filtered to online, one to in_store) and joining the results, and it guarantees the two numbers come from a single consistent snapshot of the data.

Worked example

Given orders on 2025-01-05 (online, 100), 2025-01-10 (in_store, 50), 2025-02-01 (online, 30): January shows online_revenue 100, in_store_revenue 50; February shows online_revenue 30, in_store_revenue 0 (correctly showing zero rather than NULL, since the ELSE 0 branch contributes to every row's sum even when the CASE condition is never true for that month).

Trade-offs and pitfalls

Using ELSE 0 (not leaving it implicit/NULL) is what guarantees a clean zero instead of NULL for months with no matching rows in one category; if ELSE is omitted, CASE implicitly returns NULL for non-matching rows, and SUM correctly still gives 0 in that case too (since SUM ignores NULLs), but it's clearer and safer to be explicit rather than rely on that behavior. This same technique is the foundation for building a full pivot/cross-tab with many columns, covered in a following question.

A customer lifecycle summary combines four aggregates, first and last date via MIN/MAX, count via COUNT, and total revenue via SUM, in a single GROUP BY pass, since all four are independently computable from the same grouped rows.

Structured elaboration

SELECT customer_id,
  MIN(order_date) AS first_order_date,
  MAX(order_date) AS last_order_date,
  COUNT(*) AS total_orders,
  SUM(total_amount) AS lifetime_value
FROM orders
GROUP BY customer_id;

Each of the four aggregates operates independently over the same set of grouped rows; there's no ordering dependency or intermediate step needed between them; a single scan of each customer's orders is enough to compute all four simultaneously. MIN/MAX here answer "when did this relationship start and how recently was it active"; COUNT and SUM answer "how much has this customer engaged and spent".

Worked example

Given customer 1 with orders on 2025-01-01 ($100) and 2025-03-01 ($50): the query returns first_order_date 2025-01-01, last_order_date 2025-03-01, total_orders 2, lifetime_value $150, all computed in one pass over the customer's two order rows.

Trade-offs and pitfalls

This exact four-metric shape (first, last, count, sum) is common enough to be worth recognizing as its own named pattern (a basic customer-lifecycle or RFM (recency, frequency, monetary)-adjacent summary), rather than four separate ad hoc aggregate choices; interviewers sometimes extend it by asking for a fifth derived metric, like average days between orders, which needs the dates and count already computed here as inputs to a further calculation.

ROLLUP produces a hierarchy of subtotals in one query: detail rows, then progressively higher-level subtotals, culminating in a grand total, all without running separate GROUP BY queries and UNIONing them together.

Structured elaboration

SELECT date, region, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP(date, region);

ROLLUP(date, region) produces, in one pass: detail rows grouped by (date, region), then subtotal rows grouped by date alone (with region shown as NULL), then a single grand-total row (with both date and region shown as NULL). The NULLs appearing in the grouping columns of a ROLLUP result are not missing data, they specifically mean "this row is a subtotal or total across all values of this column", a signal you can detect and label using GROUPING(). But ROLLUP is a strict, nested hierarchy in the order its arguments are listed: ROLLUP(date, region) only ever collapses region into date-level subtotals, it never independently collapses date to produce a region-only subtotal, because a by-region subtotal isn't one of the levels the date-then-region hierarchy passes through. Since the question asks for subtotals by date AND by region AND a grand total, three genuinely independent subtotal shapes, plain ROLLUP can't produce all three; GROUPING SETS is the tool for that, since it lets you list each grouping combination independently instead of only a nested hierarchy:

SELECT date, region, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS ((date, region), (date), (region), ());

Each parenthesized item in GROUPING SETS is its own independent grouping: (date, region) for the detail rows, (date) for date-only subtotals, (region) for region-only subtotals, and () for the grand total, all four computed in one pass and combined into one result.

Worked example

Given sales(date, region, product, amount) with two rows on 2025-01-01 (east: 100, west: 50) and one row on 2025-01-02 (east: 30): ROLLUP(date, region) produces the 3 detail rows, plus a subtotal for 2025-01-01 (150, summing east and west), a subtotal for 2025-01-02 (30), and a grand total row (180, summing everything), 6 rows total, but NO region-only subtotal anywhere in that output: there is no row showing east's total across both dates (130) or west's total (50) on its own.

GROUPING SETS ((date, region), (date), (region), ()) on the same data produces all of that PLUS the missing region subtotals, 8 rows total: the same 3 detail rows; date subtotals 2025-01-01 = 150 and 2025-01-02 = 30; region subtotals east = 130 (100 + 30, across both dates) and west = 50; and the grand total 180. This is the query that actually answers "subtotals by date, by region, and an overall grand total" as the question asks; ROLLUP(date, region) alone silently leaves the by-region half of that ask unmet.

Trade-offs and pitfalls

ROLLUP is hierarchical and order-sensitive: ROLLUP(date, region) rolls up region into date subtotals, not the reverse; ROLLUP(region, date) would instead produce per-region subtotals across all dates. CUBE, by contrast, produces every possible combination of subtotals (all combinations of grouping columns rolled up, not just the nested hierarchy), which is the right tool when you need cross-tabulated totals rather than a strict drill-down hierarchy.