SQL Query Fundamentals Questions

A join between campaigns and their spends, aggregated per day and compared against the campaign's own budget column, directly answers "which campaigns overspent", using the same filter-then-aggregate-then-compare-to-a-per-row-threshold shape as any budget or quota check.

Structured elaboration

SELECT c.id, c.name, SUM(s.amount) AS daily_spend, c.daily_budget
FROM campaigns c
JOIN spends s ON s.campaign_id = c.id
WHERE DATE(s.occurred_at) = :given_date
GROUP BY c.id, c.name, c.daily_budget
HAVING SUM(s.amount) > c.daily_budget;

The WHERE clause narrows to a single date's spend rows first; GROUP BY then aggregates each campaign's total spend for that day; HAVING compares the aggregated total against the campaign's own (non-aggregated, per-row-consistent) daily_budget column, which is safe to include directly in HAVING here because it's identical across every row of a given campaign's group (a functional dependency on the GROUP BY key), not a genuinely different aggregate needing its own SUM or MAX.

Worked example

Given campaign "Spring Sale" with a $100 daily budget and two spend rows on the same day, $60 and $70 (total $130): the query correctly flags this campaign as over budget, returning daily_spend $130 against daily_budget $100.

Trade-offs and pitfalls

This exact query correctly identifies campaigns that ARE over budget on a given day, retrospectively; it doesn't, by itself, prevent overspend as it happens, that requires either a real-time check before each spend is recorded or a scheduled version of this query running frequently enough to alert promptly, a monitoring/alerting design question that goes beyond the SQL itself.

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.

Detecting duplicates on a combination of columns is the same GROUP BY/HAVING COUNT(*) > 1 shape as single-column duplicate detection, just grouping on all the columns that together define "duplicate"; picking one representative row per group needs an additional aggregate like MIN(order_id).

Structured elaboration

SELECT MIN(order_id) AS representative_order_id, customer_id, order_date, total_amount, COUNT(*) AS duplicate_count
FROM orders
GROUP BY customer_id, order_date, total_amount
HAVING COUNT(*) > 1;

Grouping on all three columns (customer_id, order_date, total_amount) together means only rows that match on ALL THREE are considered duplicates of each other, not rows that merely share one of the three. MIN(order_id) picks a single, deterministic representative from each duplicate group (the row with the smallest order_id); any other deterministic tie-breaker (MAX, or a window function ordered by a timestamp) would work equally well, the specific choice just needs to be consistent and intentional, not arbitrary.

Worked example

Given two orders sharing customer_id 1, order_date 2025-01-01, and total_amount 100 (order_ids 1 and 2), plus one unrelated order for customer 2: the query correctly identifies the (1, 2025-01-01, 100) combination as a duplicate pair (duplicate_count 2), picks order_id 1 as the representative, and correctly excludes the unrelated customer 2 order entirely (its combination appears only once, so COUNT(*) > 1 fails and it's filtered out by HAVING).

Trade-offs and pitfalls

A window function alternative achieves the same deduplication and additionally makes it straightforward to keep the FULL row data of the representative, not just its id, in a single pass. PARTITION BY groups rows the same way GROUP BY does, splitting the table into buckets sharing the same (customer_id, order_date, total_amount) combination, but instead of collapsing each bucket down to one summary row the way GROUP BY does, ROW_NUMBER() OVER (PARTITION BY customer_id, order_date, total_amount ORDER BY order_id) labels every row within its bucket 1, 2, 3... in the ORDER BY order (here, by order_id ascending), so wrapping this in an outer query and keeping only WHERE rn = 1 selects exactly one full row per duplicate group, the lowest-order_id row, while every other column from that row comes along for free, not just order_id:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id, order_date, total_amount ORDER BY order_id) AS rn
  FROM orders
) t
WHERE rn = 1;

a genuinely useful alternative once your dedup logic needs more than an id and a count.

Combining five summary statistics per entity with a minimum-activity threshold is the WHERE-then-GROUP-BY-then-HAVING pattern applied to a richer aggregate set: filter to the relevant time window first, compute all five stats per user in one pass, then keep only users who cleared the activity bar.

Structured elaboration

SELECT user_id, SUM(amount) AS total, AVG(amount) AS average,
       MIN(amount) AS minimum, MAX(amount) AS maximum, COUNT(*) AS n
FROM transactions
WHERE occurred_at >= (reference_date - interval '90 days')
GROUP BY user_id
HAVING COUNT(*) >= 3
ORDER BY total DESC;

All five aggregates (SUM, AVG, MIN, MAX, COUNT) are computed in a single GROUP BY pass over the same filtered rows; the HAVING clause then discards any user whose transaction count over the window falls short of the minimum, without needing a second query or a self-join. (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 the transactions schema.)

Worked example

Given user 1 with three transactions (10, 20, 30) and user 2 with one transaction (15), all within the 90-day window: user 1 passes the COUNT(*) >= 3 threshold (total 60, average 20, min 10, max 30), while user 2 is excluded entirely for having only one transaction, despite being a real, valid row within the time window. Add a fourth transaction for user 1, amount 1000, dated 120 days ago, outside the 90-day window: the WHERE occurred_at >= (reference_date - interval '90 days') filter excludes it before GROUP BY/HAVING ever run, so user 1's total stays correctly at 60, not 1060, unaffected by the old transaction's presence in the table.

Trade-offs and pitfalls

A common mistake is applying the minimum-count threshold in WHERE instead of HAVING, which doesn't even parse (WHERE can't reference COUNT(*), an aggregate that doesn't exist until after GROUP BY runs); recognizing this as the same WHERE-vs-HAVING distinction (WHERE filters individual rows before grouping, HAVING filters the grouped results afterwards), just with a richer aggregate payload, is the transferable insight.

NULL means "no value recorded"; an empty string '' is an actual, present value that happens to contain zero characters. They are not the same, and IS NULL will not match an empty string (nor will = '' match a NULL).

Structured elaboration

-- Missing (NULL) values
SELECT contact_id FROM contacts WHERE phone IS NULL OR email IS NULL;
-- Empty-string values
SELECT contact_id FROM contacts WHERE phone = '' OR email = '';

These are two genuinely different queries returning two genuinely different sets of rows, because SQL treats "no value was ever recorded" (NULL) and "a value was recorded, and it's the empty string" ('') as distinct states. Using = NULL to try to catch both (or either) fails outright: = NULL is never TRUE, because NULL represents an unknown value, and asking whether an unknown value equals NULL is itself an unanswerable question; SQL returns UNKNOWN rather than TRUE or FALSE for that comparison, and a WHERE clause only keeps rows where the predicate evaluates to TRUE, so a row with a genuinely NULL phone or email is silently excluded by = NULL even though it's exactly the row being searched for.

Worked example

Given contacts(contact_id, email, phone) with rows (1, NULL, '555'), (2, 'a@x.com', ''), (3, 'b@x.com', '555'): the NULL-checking query returns only contact 1 (NULL email). The empty-string-checking query returns only contact 2 (empty phone). Neither query alone catches both "missing" representations.

Trade-offs and pitfalls

If a system genuinely uses both NULL and '' interchangeably to mean "missing" (common when data is ingested from a CSV or a form that submits blank strings instead of omitting the field), a "find missing contacts" query needs both conditions explicitly combined: WHERE phone IS NULL OR phone = '', and ideally the underlying inconsistency should be fixed at the source rather than patched at every downstream query.