SQL Query Fundamentals Questions

The stored value '2024-03-10 09:15:00' is not equal to the literal string '2024-03-10', because the column carries a time-of-day component the literal doesn't; either truncate the column to just its date part, or use a half-open range to cover the whole day.

Structured elaboration

-- Fix 1: truncate the column to a date
SELECT * FROM hires WHERE DATE(hire_date) = '2024-03-10';
-- Fix 2: half-open range covering the whole day
SELECT * FROM hires WHERE hire_date >= '2024-03-10' AND hire_date < '2024-03-11';

Both are correct; they differ in a performance consideration worth naming even though it's not the core of the answer: wrapping the column in a function (DATE(hire_date)) can prevent the engine from using a plain index on hire_date, since the index was built on the raw timestamp values, not their truncated form. The range-based fix (>= AND <) can use a standard index on hire_date directly, with no function wrapping needed.

Worked example

Given a single row with hire_date = '2024-03-10 09:15:00': WHERE hire_date = '2024-03-10' returns zero rows (a straightforward string/value mismatch against the stored timestamp). Both fixes correctly return the one row.

Trade-offs and pitfalls

This is the single-day special case of the more general date-range-filtering pitfall (BETWEEN across a multi-day range has the identical underlying cause); recognizing the two as the same root issue, rather than two unrelated tricks to memorize, is what a senior explanation looks like.

Joining a customers table directly to order_items and summing produces an inflated total whenever a customer has more than one order, because each order's line items get re-multiplied by however many order rows are being joined through; pre-aggregating order_items to order-level totals first, then joining up, avoids the inflation.

Structured elaboration

-- WRONG shape (works fine for a single order, but is a latent bug)
SELECT c.id, SUM(oi.qty * oi.unit_price) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY c.id;

-- SAFE shape: pre-aggregate order_items to order totals, then join up
WITH order_totals AS (
  SELECT order_id, SUM(qty * unit_price) AS order_revenue
  FROM order_items GROUP BY order_id
)
SELECT c.id, COUNT(o.order_id) AS order_count, SUM(ot.order_revenue) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_totals ot ON ot.order_id = o.order_id
GROUP BY c.id;

The bug in the first shape isn't in the SUM itself, it's that the join produces one row per (order, order_item) pair, and a customer-level COUNT or a mis-scoped aggregate over that joined result silently reflects the item-level grain, not the order-level or customer-level grain you're mentally picturing.

Worked example

A customer with one order containing two line items ($100 and $30, order total $130): a naive COUNT(o.order_id) after joining through order_items returns 2 (one count per line item), not 1 (the actual number of orders), even though SUM(qtyunit_price) happens to still total correctly to $130 in this single-order case. Pre-aggregating order_items to order totals first, then joining, correctly reports order_count = 1 and revenue = $130. The risk compounds with more orders: any customer-level COUNT taken directly off the item-joined result will always be inflated by however many items each order has, while SUM(qtyunit_price) itself stays numerically correct only because multiplication and summation happen to commute here, a coincidence that doesn't extend to non-additive metrics like COUNT or AVG taken at the wrong grain.

Trade-offs and pitfalls

The general rule: whenever a query joins through a one-to-many (or many-to-many) relationship, ask explicitly "what does one row of this joined result actually represent" before writing any aggregate on top of it, and pre-aggregate to the coarser grain BEFORE joining up to an even coarser one, rather than aggregating across every level in a single pass.

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.

GROUP BY treats all NULL values in the grouping column as a single group, distinct from every non-NULL value; COALESCE lets you give that group a readable label.

Structured elaboration

SELECT COALESCE(coupon_code, 'NO_COUPON') AS coupon, COUNT(*) AS uses
FROM orders
GROUP BY COALESCE(coupon_code, 'NO_COUPON');

This groups all rows with a NULL coupon_code together under the label 'NO_COUPON', alongside the real coupon codes as their own groups. Without the COALESCE, the NULL group would still appear as its own row in the result, just displayed as a literal NULL, which is fine for internal analysis but reads poorly in a report.

Worked example

Given orders(coupon_code, amount) with rows ('SAVE10', 20), (NULL, 15), (NULL, 30), ('SAVE10', 10): grouping produces two rows: NO_COUPON with 2 uses, and SAVE10 with 2 uses. The two NULL rows are correctly folded into one group, not treated as two separate unmatched groups or dropped.

Trade-offs and pitfalls

GROUP BY NULL-as-one-group is standard, consistent behavior across engines (unlike, say, sort order for NULLs, which varies), so it's safe to rely on. The judgment call is purely about presentation: COALESCE for a label works well for a simple report; if the NULL group needs its own downstream analysis (e.g., understanding why so many orders have no coupon at all), keep it un-labeled and filter to it directly with WHERE coupon_code IS NULL instead of hiding it inside a labeled aggregate.

At least four distinct reasons a filter that "should" match can return zero rows: a type/format mismatch (customer_id stored as text with leading zeros or whitespace, compared to a bare integer), a NULL-related predicate elsewhere in the query silently dropping rows, an implicit filter from a JOIN turning into an INNER join, or simply querying the wrong schema, table, or environment (a replica lagging behind, or a staging database that looks identical to production).

Structured elaboration

A systematic debugging checklist, roughly in order of how cheap each check is:

1. Confirm you're querying the right place. SELECT current_database(), check the connection's schema search path, and confirm you're not accidentally pointed at a read replica that hasn't caught up yet.
2. Isolate the WHERE clause. Run SELECT * FROM orders WHERE customer_id = 12345 with no other joins or filters. If that alone returns nothing, the problem is in the predicate or the data, not elsewhere in the query.
3. Check for a type/format mismatch. SELECT customer_id, typeof(customer_id) FROM orders LIMIT 5 (or the engine's equivalent) to confirm the column isn't storing '12345 ' (trailing space) or '012345' as text, which won't equality-match a bare integer literal.
4. Check if a JOIN elsewhere in the real query silently filtered the row out. A LEFT JOIN followed by a WHERE clause on the joined table's columns behaves like an INNER JOIN (a separate, common trap).
5. Confirm the row genuinely exists in this exact table, not a similarly-named one, and that no row-level security policy or view definition is filtering it out invisibly.

Worked example

Given orders(customer_id TEXT) with a single row '12345 ' (trailing space): WHERE customer_id = '12345' returns zero rows, while WHERE TRIM(customer_id) = '12345' correctly finds it, confirming a whitespace mismatch as one concrete instance of reason #3.

Trade-offs and pitfalls

The instinct to immediately rewrite the query is usually premature; the fastest path is isolating variables one at a time (drop joins, drop extra filters, check raw data) rather than guessing. This checklist format is itself a useful thing to state out loud in an interview: it signals systematic debugging instinct rather than one lucky guess.