SQL Query Fundamentals Questions

When an engine has no native PIVOT operator, conditional aggregation (SUM wrapped in CASE, one CASE per target column) builds the same rows-to-columns transformation manually.

Structured elaboration

SELECT product_id,
  SUM(CASE WHEN strftime('%Y-%m', sold_date) = '2025-01' THEN amount ELSE 0 END) AS "2025-01",
  SUM(CASE WHEN strftime('%Y-%m', sold_date) = '2025-02' THEN amount ELSE 0 END) AS "2025-02",
  SUM(CASE WHEN strftime('%Y-%m', sold_date) = '2025-03' THEN amount ELSE 0 END) AS "2025-03",
  SUM(CASE WHEN strftime('%Y-%m', sold_date) = '2025-04' THEN amount ELSE 0 END) AS "2025-04",
  SUM(CASE WHEN strftime('%Y-%m', sold_date) = '2025-05' THEN amount ELSE 0 END) AS "2025-05",
  SUM(CASE WHEN strftime('%Y-%m', sold_date) = '2025-06' THEN amount ELSE 0 END) AS "2025-06"
FROM sales
GROUP BY product_id;

Each output column is its own conditional SUM, so this is really the same conditional-aggregation technique from splitting one metric into two channel columns, extended to as many columns as there are target months. The month list has to be known and written out at query-authoring time; there is no way to make the number of output columns dynamic in standard SQL, since the SELECT list's shape is fixed at parse time. Concretely, if today is 2025-06-15 and "last 6 months" means the trailing 6 calendar months ending at the current one, the six columns to write out are 2025-01 through 2025-06, exactly the six shown above; next month, the same query would need to be edited by hand to shift the window forward to 2025-02 through 2025-07, since nothing about which months are covered is computed automatically.

Worked example

Given product 1 with sales of 100 in January, 50 in February, 20 in March, and 40 in April (no sales in May or June): the query returns one row for product 1 with columns 2025-01 = 100, 2025-02 = 50, 2025-03 = 20, 2025-04 = 40, 2025-05 = 0, and 2025-06 = 0, correctly attributing each month's revenue to its own column and showing an explicit 0, not an absent column, for the two months with no sales at all.

Trade-offs and pitfalls

This hardcoded-column approach doesn't scale past a small, known, relatively stable set of categories (a handful of months, a fixed set of regions); for a genuinely dynamic or large category set, the more maintainable options are either building the pivot SQL dynamically outside the database (generating the CASE list programmatically before sending the query) or handling the pivot in the BI/reporting tool layer instead of in SQL at all.

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).

DISTINCT deduplicates whole rows (or whole selected column sets); it can't compute anything, which is exactly where GROUP BY with an aggregate becomes necessary rather than optional.

Structured elaboration

"What is the difference between GROUP BY and DISTINCT" has a simple case where they overlap: for a plain single-column distinct list with no aggregate involved, SELECT DISTINCT category FROM sales and SELECT category FROM sales GROUP BY category return the identical set of rows. For example, given categories 'electronics', 'toys', 'electronics', both return exactly the two rows 'electronics' and 'toys', with no computed value attached to either. And there's a case where they clearly don't overlap: any time you need a computed value per group, not just the group's identity. SELECT DISTINCT category FROM sales and SELECT category, COUNT(*) FROM sales GROUP BY category both group the same way internally, but only the second one can attach a count, sum, or average to each category. There is no DISTINCT-based way to also report "how many sales rows are in each category" without introducing GROUP BY (or an equivalent subquery/window function) somewhere.

Worked example

Wanting "each distinct product category, and how many products are in it" requires GROUP BY: SELECT category, COUNT(*) AS n FROM products GROUP BY category. SELECT DISTINCT category, COUNT(*) isn't valid syntax in most engines when mixing DISTINCT with an aggregate this way, and even where it parses, it doesn't mean what you'd want.

Trade-offs and pitfalls

A quick test for which tool fits: if the question has the word "how many" or any other aggregate verb (total, average, most recent) attached to each group, GROUP BY is required; if it's purely "list the unique values of X", DISTINCT is suf ficient and simpler.

The zero-fill technique generalizes from time periods to any dimension: joining from a complete, independent list of dimension values (every country, not just the ones with activity) is what guarantees zero-activity dimension values appear explicitly rather than being silently absent.

Structured elaboration

SELECT u.country, COALESCE(SUM(o.amount), 0) AS total_revenue,
       COUNT(DISTINCT o.user_id) AS unique_buyers
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id AND o.occurred_at >= (reference_date - interval '30 days')
GROUP BY u.country;

(reference_date stands in for CURRENT_DATE/CURRENT_TIMESTAMP, the moment the report is run; substitute it directly, or replace it with CURRENT_DATE outright, since it isn't a real column in either table.)

Here the "complete axis" is the distinct set of countries known from the users table (or a dedicated countries dimension table), not a generated date series; the same LEFT JOIN-from-the-complete-side principle applies: start from every country that should be represented, then LEFT JOIN the actual order activity onto it, so a country with zero recent orders still appears with revenue = 0 rather than being silently absent from the report.

The 30-day filter has to live inside the JOIN's ON clause, exactly as written above, not in a separate WHERE clause. Since this query needs to keep every country (via LEFT JOIN) even when it has zero recent orders, moving o.occurred_at >= (reference_date - interval '30 days') into WHERE would evaluate it AFTER the join has already happened, and WHERE only keeps rows where the condition is TRUE. For a country with no matching orders at all, the joined order columns are already NULL, so a WHERE condition comparing o.occurred_at to a date evaluates to UNKNOWN for that row, which WHERE treats the same as FALSE, silently dropping every zero-order country from the result. That turns the LEFT JOIN back into an effective INNER JOIN for exactly the countries this query exists to keep. Keeping the condition in ON instead restricts WHICH orders get joined in the first place, before the LEFT JOIN's NULL-padding behavior for unmatched countries even applies, so a country with no qualifying orders still gets exactly one output row, NULL-padded, rather than being filtered out entirely.

Worked example

Given users Ann (US) and Bo (US) and Cy (Canada), with two US orders in the last 30 days ($80 for Ann, $70 for Bo) and zero Canada orders: the query above correctly returns US with total_revenue = 150 and unique_buyers = 2, and Canada with total_revenue = 0 and unique_buyers = 0, exactly the zero-fill behavior the question asks for. Moving the same date condition into a WHERE clause instead, ... LEFT JOIN orders o ON o.user_id = u.user_id WHERE o.occurred_at >= (reference_date - interval '30 days') ..., silently drops Canada from the result entirely: US still shows 150/2, but Canada's LEFT-JOIN-produced NULL row fails the WHERE condition and vanishes, exactly the bug this pattern is built to avoid.

Trade-offs and pitfalls

The subtlety worth calling out explicitly: this is zero-filling over a DIMENSION (every country that exists), not over TIME (every month that exists), the distinction from the previous question. Both use the identical "start from the complete side, LEFT JOIN the activity" mechanic; recognizing the shared shape across dimension-completeness and time-completeness is more valuable than treating them as two unrelated tricks.

A distinct-count-per-period metric like Daily Active Users needs COUNT(DISTINCT user_id) specifically, not a plain COUNT, because a plain COUNT would count every event, over-counting any user who was active more than once in the same period.

Structured elaboration

SELECT DATE(occurred_at) AS day, COUNT(DISTINCT user_id) AS dau
FROM events
GROUP BY day;

COUNT(DISTINCT user_id) deduplicates within each day's group, so a user who logged three events on the same day still contributes exactly 1 to that day's count, correctly answering "how many distinct users were active", not "how many events happened". The GROUP BY-based alternative pushes the deduplication into an inner query instead of an aggregate function: SELECT day, COUNT(*) AS dau FROM (SELECT DISTINCT DATE(occurred_at) AS day, user_id FROM events) t GROUP BY day. The inner SELECT DISTINCT collapses each user down to at most one row per day first, so by the time the outer COUNT(*) runs there's nothing left to deduplicate, it's just counting already-unique rows.

Worked example

Given events for user 1 twice and user 2 once, all on 2025-01-01, and user 3 once on 2025-01-02: the query correctly returns DAU of 2 for 2025-01-01 (users 1 and 2, with user 1's repeat event not double-counted) and 1 for 2025-01-02 (user 3).

Trade-offs and pitfalls

Both forms are logically equivalent and, on the worked example below, return the identical numbers; which one an optimizer executes faster can differ by engine and data distribution, so it's worth trying both against real data rather than assuming one is always better.

COUNT(DISTINCT ...) is measurably more expensive than a plain COUNT at large scale, since the engine typically needs to sort or hash the values to deduplicate them; for very high-cardinality, very large-scale distinct counting (billions of events per day), an approximate distinct-count function (HyperLogLog-based, such as Postgres's hll extension or BigQuery's APPROX_COUNT_DISTINCT) trades a small, bounded error for dramatically better performance, a trade-off worth naming even though it's outside this topic's core scope.