SQL Query Fundamentals Questions

Labeling the NULL group with COALESCE is a display-layer fix; it's worth thinking through separately from whether NULLs should also be preserved for analysis, and from what happens when that labeled value later needs to join back to a real dimension table.

Structured elaboration

SELECT COALESCE(customer_segment, 'Unknown') AS segment, COUNT(*) AS n
FROM customers
GROUP BY COALESCE(customer_segment, 'Unknown');

Two considerations beyond the basic COALESCE-for-display pattern:

1. Keeping the NULL group visible for analysis: sometimes you want both the labeled dashboard view AND a way to drill into "why do these customers have no segment at all" (a data-quality question in its own right), so don't let the COALESCE'd label become the only representation of that data downstream. Concretely, run a dedicated query alongside the COALESCE one: SELECT * FROM customers WHERE customer_segment IS NULL. Against the worked-example data below (customers 2 and 4), this returns exactly those two rows in full, letting an analyst drill into who they are and why their segment is missing, something the COALESCE'd dashboard view alone can't answer since it has already collapsed both rows into a single 'Unknown' count.
2. Joining back to a segment dimension table: if 'Unknown' is joined against a real segments dimension table that has no row for the literal string 'Unknown', the join will silently drop those rows (an INNER JOIN) or produce NULLs for the dimension's other columns (a LEFT JOIN), which is a second, distinct NULL-handling decision layered on top of the first.

Worked example

Given customers(customer_id, customer_segment) with rows (1,'SMB'), (2,NULL), (3,'Enterprise'), (4,NULL): the query correctly groups the two NULL customers under 'Unknown', alongside SMB (1) and Enterprise (1), for three total groups.

Trade-offs and pitfalls

The failure mode to watch for is compounding: COALESCE handles the grouping and display, but if a later join to a dimension table isn't also NULL-aware (or Unknown-aware), the same underlying "missing segment" data problem resurfaces as a second, harder-to-spot bug at the join step.

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.

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.

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.

A TIMESTAMP column includes a time-of-day component, so a date-only BETWEEN upper bound like '2024-03-31' is treated as midnight at the start of that day, silently excluding every row later that same day; a half-open range using the next day's start avoids the gap.

Structured elaboration

-- Misses late-March-31 rows
SELECT order_id FROM orders WHERE created_at BETWEEN '2024-03-01' AND '2024-03-31';
-- Correct half-open range
SELECT order_id FROM orders WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01';

BETWEEN '2024-03-01' AND '2024-03-31' is shorthand for >= '2024-03-01' AND <= '2024-03-31', and that upper bound, when compared against a TIMESTAMP, is interpreted as 2024-03-31 00:00:00. Any row timestamped later that same day (say, 2024-03-31 23:00:00) is strictly greater than midnight and gets excluded, even though it clearly belongs in "March 2024" by any reasonable interpretation.

Worked example

Given orders at 2024-03-31 23:00:00 and 2024-03-15 10:00:00: the BETWEEN version returns only the March 15 order, silently dropping the March 31 order entirely. The half-open version (< '2024-04-01') correctly returns both orders.

Trade-offs and pitfalls

The half-open-range fix generalizes to any period boundary (a single day, a month, a quarter, a year): always express the upper bound as "the start of the next period, exclusive" rather than "the last moment of this period, inclusive", since the latter requires knowing the column's precision exactly (is it seconds? milliseconds? does '2024-03-31 23:59:59' actually cover a row at '2024-03-31 23:59:59.500'?) while the former never does. This same half-open form is also what makes the query index-friendly, which the question asks for by name: created_at >= '2024-03-01' AND created_at < '2024-04-01' compares the raw created_at column directly against two literal bounds, exactly the shape a B-tree index (the default index structure in most relational engines, storing column values in sorted order) on created_at can use for a plain range scan. A common alternative fix, wrapping the column instead of the bound, e.g. WHERE DATE(created_at) BETWEEN '2024-03-01' AND '2024-03-31' or CAST(created_at AS DATE) = ..., looks similarly correct but applies a function to every row's created_at value before comparing it, which prevents a plain index on created_at from being used at all, since the engine can no longer look up sorted values directly and instead has to compute the expression for every row. Keeping the column bare on one side of a plain comparison is what index-friendly means in practice.