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.

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.

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.

Comparing or grouping values that look numerically identical but are stored with different types or representations, a numeric string '1' versus '01' versus '1.0', can produce more groups than intended, because GROUP BY compares the stored representation, not a normalized numeric interpretation of it.

Structured elaboration

-- Grouping on the raw text produces 3 separate groups
SELECT k, COUNT(*) FROM t GROUP BY k;
-- Standardizing to a numeric type first collapses them correctly
SELECT CAST(k AS REAL) AS k_num, COUNT(*) FROM t GROUP BY k_num;

If a column meant to represent a number is stored as TEXT, the values '1', '01', and '1.0' are three genuinely different strings, and GROUP BY on the raw text treats them as three separate groups, even though they represent the identical number. Casting to a genuine numeric type before grouping standardizes all three to the same underlying value, collapsing them correctly into one group.

Worked example

Given a text column with values '1', '01', '1.0': grouping on the raw text produces three separate rows, each with count 1. Casting to REAL first and grouping on the cast value produces one row (1.0) with count 3, correctly recognizing all three as the same number.

Trade-offs and pitfalls

The general lesson: standardize the TYPE of a grouping key at the point it's first captured or loaded (store numbers as numbers, not as text), rather than patching every downstream query with a CAST; a CAST-in-every-query workaround is a sign the underlying schema or ingestion pipeline has a type-modeling gap that would be worth fixing at the source. Floating-point columns specifically carry a second, related but distinct risk: two values that are mathematically equal can still differ in their last few bits of binary representation, requiring a ROUND (not a CAST) to standardize before grouping. For example, two rows computed as 0.1 + 0.2 and a literal 0.3 are mathematically equal, but under IEEE 754 double-precision floating point (the standard representation most engines use for FLOAT/REAL/DOUBLE columns), 0.1 + 0.2 is actually stored as 0.30000000000000004, one bit off from the literal 0.3.

-- Grouping on the raw float value produces 2 groups instead of 1
SELECT k, COUNT(*) FROM t GROUP BY k;
-- Rounding to a fixed number of decimal places collapses them correctly
SELECT ROUND(k, 2) AS k_rounded, COUNT(*) FROM t GROUP BY k_rounded;

Given t(k) with the two rows above: grouping on the raw k produces two separate rows, each with count 1 (0.3 and 0.30000000000000004 are treated as different group keys). Grouping on ROUND(k, 2) instead produces one row, 0.3 with count 2, correctly recognizing both as the same number once rounded to a sane precision. A plain CAST doesn't fix this the way it fixed the string-versus-numeric case: casting a float to another float type doesn't remove the representation error, only ROUND (or an equivalent fixed-precision truncation) does, because it deliberately discards the bits beyond the chosen precision rather than just changing the storage type.

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.