SQL Query Fundamentals Questions

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.

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

IN (subquery), EXISTS (correlated subquery: correlated means the subquery reaches back into the outer query's current row, here v.customer_id = o.customer_id ties each check to whichever order row is currently being evaluated, so conceptually it re-checks for a match once per outer row, unlike the IN subquery above, which runs once, independently, and produces one fixed list that every outer row is then compared against), and an explicit JOIN can all express "rows that have a related match", and for a simple existence check like this they're logically equivalent; the choice between them is mostly about readability and how the optimizer handles duplicates.

Structured elaboration

-- IN
SELECT order_id FROM orders WHERE customer_id IN (SELECT customer_id FROM vip_customers);
-- EXISTS
SELECT order_id FROM orders o WHERE EXISTS (SELECT 1 FROM vip_customers v WHERE v.customer_id = o.customer_id);
-- JOIN
SELECT DISTINCT o.order_id FROM orders o JOIN vip_customers v ON v.customer_id = o.customer_id;

IN and EXISTS both naturally return each qualifying order exactly once, even if vip_customers somehow had a duplicate customer_id row, because neither actually joins the row data, they just test for existence. A plain JOIN, by contrast, would return a duplicate order row for each matching vip_customers row if that table had duplicates, requiring an explicit DISTINCT to correct, since JOIN genuinely combines row data rather than testing existence.

Worked example

Given orders for customer 1 ($50) and customer 2 ($60), with only customer 1 in vip_customers: all three approaches correctly return only order 1. If vip_customers instead had two rows for customer_id 1 (a data-quality duplicate), the JOIN version would return order 1 twice unless DISTINCT is added, while IN and EXISTS would still correctly return it once.

Trade-offs and pitfalls

For large tables, most modern optimizers rewrite IN, EXISTS, and JOIN-with-DISTINCT to nearly identical execution plans when the intent is a pure existence check, so the choice is mostly stylistic; EXISTS is often considered the clearest expression of "does a related row exist" specifically because it can't accidentally introduce duplicate rows the way a raw JOIN can.

GROUP BY 1, 2 (positional) is terser but fragile under refactoring; repeating the full expression is explicit but verbose and error-prone to keep in sync; a CTE (Common Table Expression) with a named alias is usually the most maintainable middle ground.

Structured elaboration

-- Positional: fragile if SELECT list columns get reordered
SELECT region, DATE_TRUNC('month', order_date), SUM(amount) FROM orders GROUP BY 1, 2;
-- Repeated expression: verbose, and easy to accidentally diverge from the SELECT list
SELECT region, DATE_TRUNC('month', order_date) AS month, SUM(amount)
FROM orders GROUP BY region, DATE_TRUNC('month', order_date);
-- CTE with alias: compute once, group by the readable name
WITH base AS (
  SELECT region, DATE_TRUNC('month', order_date) AS month, amount FROM orders
)
SELECT region, month, SUM(amount) FROM base GROUP BY region, month;

Positional GROUP BY is standard SQL and works everywhere, but if someone later reorders or adds a column to the SELECT list, the grouping silently changes to match different columns, a genuinely dangerous refactoring hazard. Repeating the expression avoids that but means the transformation logic is duplicated and can drift if only one copy is edited. A CTE materializes the computed expression once under a name, so both the SELECT list and the GROUP BY reference the same alias with no duplication.

Worked example

Given orders(region, order_date, amount) with rows (US, 2024-01-05, 100), (US, 2024-01-20, 50), (EU, 2024-01-10, 30), (US, 2024-02-01, 40): the original query, SELECT region, DATE_TRUNC('month', order_date), SUM(amount) FROM orders GROUP BY 1, 2, correctly returns three rows: EU/January 30, US/January 150, US/February 40.

Now suppose a teammate inserts customer_segment as a new first column in the SELECT list (every row here happens to be segment 'B2C') and forgets to update the GROUP BY: SELECT customer_segment, region, DATE_TRUNC('month', order_date), SUM(amount) FROM orders GROUP BY 1, 2. The GROUP BY clause still says 1, 2, but those ordinals now point at customer_segment and region, not region and the month expression. Because the third column (the month) is no longer part of the grouping, engines diverge sharply on what happens: strict engines like PostgreSQL raise an error (every non-aggregated SELECT column must appear in GROUP BY), while others (SQLite, and MySQL outside strict mode) silently pick an arbitrary month value per group and merge everything else. On the latter, the result silently collapses to two rows, B2C/EU 30 and B2C/US 190, with the January 150 and February 40 for US silently summed together into one bucket, no error and no warning that two different months got merged.

Trade-offs and pitfalls

For a one-off, throwaway query, positional GROUP BY is fine and common in ad hoc analysis. For anything that will live in a dashboard, a scheduled report, or a codebase, prefer the CTE-with-alias pattern: it reads clearly and is safe under later edits to the SELECT list.

A basic INNER JOIN combined with a WHERE filter on a joined-table column returns only rows that both match across tables and satisfy the filter, in the order the query is naturally read: join first (conceptually), then filter.

Structured elaboration

SELECT u.name, u.email, o.order_id, o.total_amount
FROM users u
JOIN orders o ON o.user_id = u.user_id
WHERE o.total_amount > 100;

Because this is an INNER JOIN (the default JOIN type), only users with at least one qualifying order appear at all; a user with zero orders, or only orders under $100, is excluded entirely, not shown with a NULL order. This is the correct behavior for "list users along with their qualifying orders", but would be the wrong choice for "list all users and flag which ones have a qualifying order" (which needs a LEFT JOIN instead).

Worked example

Given users Ann (order of $150) and Bo (order of $50): the query returns only Ann's row, since Bo's only order fails the > 100 filter and Bo has no other order to fall back on.

Trade-offs and pitfalls

The WHERE filter here is evaluated after the join produces a row, so filtering on o.total_amount this way only excludes joined rows that don't qualify; it does not, by itself, change whether unmatched left-side rows would appear (that's controlled entirely by the join type, INNER vs LEFT). Confusing these two independent decisions, join type and WHERE filter, is one of the most common sources of a subtly wrong query.