SQL Query Fundamentals Questions

A customer lifecycle summary combines four aggregates, first and last date via MIN/MAX, count via COUNT, and total revenue via SUM, in a single GROUP BY pass, since all four are independently computable from the same grouped rows.

Structured elaboration

SELECT customer_id,
  MIN(order_date) AS first_order_date,
  MAX(order_date) AS last_order_date,
  COUNT(*) AS total_orders,
  SUM(total_amount) AS lifetime_value
FROM orders
GROUP BY customer_id;

Each of the four aggregates operates independently over the same set of grouped rows; there's no ordering dependency or intermediate step needed between them; a single scan of each customer's orders is enough to compute all four simultaneously. MIN/MAX here answer "when did this relationship start and how recently was it active"; COUNT and SUM answer "how much has this customer engaged and spent".

Worked example

Given customer 1 with orders on 2025-01-01 ($100) and 2025-03-01 ($50): the query returns first_order_date 2025-01-01, last_order_date 2025-03-01, total_orders 2, lifetime_value $150, all computed in one pass over the customer's two order rows.

Trade-offs and pitfalls

This exact four-metric shape (first, last, count, sum) is common enough to be worth recognizing as its own named pattern (a basic customer-lifecycle or RFM (recency, frequency, monetary)-adjacent summary), rather than four separate ad hoc aggregate choices; interviewers sometimes extend it by asking for a fifth derived metric, like average days between orders, which needs the dates and count already computed here as inputs to a further calculation.

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.

DELETE removes rows individually and can be filtered, rolled back, and will fire row-level triggers; TRUNCATE removes every row at once, is typically not filterable, is much faster since it deallocates storage pages rather than deleting row by row, and often can't be rolled back the same way (or fires no row-level triggers); DROP removes the table's structure entirely, not just its data.

Structured elaboration

• DELETE [FROM table WHERE ...]: a normal, logged, transactional operation. Can target a subset of rows via WHERE, participates in a transaction (rollback restores the deleted rows), and fires any row-level triggers defined on the table, once per deleted row.
• TRUNCATE TABLE: removes ALL rows (most engines don't support a WHERE clause on TRUNCATE at all), is minimally logged compared to DELETE (deallocating whole data pages rather than logging each row deletion), and is correspondingly much faster on a large table; in PostgreSQL it IS transactional and can be rolled back, but in some other engines it behaves as an auto-committing DDL (Data Definition Language: schema-altering commands like CREATE, ALTER, and DROP, as opposed to DML (Data Manipulation Language) commands like DELETE and INSERT that only touch row data)-like operation that cannot be rolled back once issued; row-level triggers typically do not fire for TRUNCATE, only certain engines' statement-level or TRUNCATE-specific triggers do.
• DROP TABLE: removes the table itself, structure, data, indexes, and constraints entirely; there's no table left to query afterward. On transactional behavior: DROP is DDL like TRUNCATE, and shares the same engine split, in PostgreSQL it runs inside a transaction block and can be rolled back like any other statement, while in some other engines (e.g., MySQL) it auto-commits immediately as DDL and cannot be undone once issued. On performance: DROP is at least as fast as TRUNCATE, typically faster, since it doesn't even need to keep an empty table structure around afterward, it just removes the storage and the catalog entry describing the table. On triggers: DROP fires no triggers at all, not row-level and not statement-level, because no row data is being touched, only the table's definition is being removed.
• Permissions: DELETE typically requires DELETE privilege on the table; TRUNCATE and DROP typically require a higher privilege level (often table-owner or a dedicated TRUNCATE/DROP grant), reflecting how much more destructive and irreversible-in-practice they are.

Worked example

Deleting all rows from a 10-million-row table with DELETE FROM t (no WHERE) works but is slow (logging each row individually) and, depending on engine and vacuum settings, may not immediately reclaim disk space; TRUNCATE TABLE t accomplishes the same "empty the table" goal far faster by deallocating storage directly, at the cost of losing per-row filtering and, on some engines, easy rollback.

Trade-offs and pitfalls

The practical decision rule: need to remove a SUBSET of rows, or need transactional rollback safety with certainty across all engines? Use DELETE. Need to empty an ENTIRE table fast and don't need row-level triggers to fire? TRUNCATE. Need the table to no longer exist at all, structure included? DROP. Confusing TRUNCATE for a "faster DELETE with a WHERE clause" is the most common practical mistake, since most engines simply don't support that.

ORDER BY revenue DESC LIMIT 5 gets the top 5 by revenue, but ties at the boundary are returned in whatever order the engine happens to produce unless a secondary sort column breaks them deterministically.

Structured elaboration

SELECT product_id, SUM(price * quantity) AS revenue
FROM order_items
GROUP BY product_id
ORDER BY revenue DESC, product_id ASC
LIMIT 5;

Adding product_id ASC as a secondary sort key means that if two products tie exactly on revenue, they always sort in the same relative order (lower product_id first) every time the query runs, rather than depending on incidental factors like row insertion order or which parallel worker returned its partial result first, both of which can vary run to run without a tie-breaker.

Worked example

Given order_items for products 10, 11, and 12 with computed revenues 100, 100, and 10 respectively: without a tie-breaker, products 10 and 11 could appear in either order across repeated runs. With ORDER BY revenue DESC, product_id ASC, the result is deterministically product 10, then product 11 (both at revenue 100, lower id first), then product 12.

Trade-offs and pitfalls

This matters more than it looks: a "top 5" report that silently reorders itself between refreshes (with no underlying data change) erodes trust in the dashboard, and pagination built on an undeterministic sort can skip or repeat rows across pages. For a genuine "top N per some other dimension" question (e.g., top N products per category), a window function like RANK() OVER (PARTITION BY ...) is the more natural tool, and ties can be surfaced explicitly rather than silently broken. Concretely, extending this worked example to 6 products with revenues 500, 400, 300, 200, 100, 100 (the last two tied): ORDER BY revenue DESC, product_id ASC LIMIT 5 still returns exactly 5 rows, picking the lower product_id of the tied pair and silently dropping the other. SELECT product_id, revenue, RANK() OVER (ORDER BY revenue DESC) AS rnk FROM ... instead assigns both tied products rank 5, so filtering WHERE rnk <= 5 returns 6 rows: both tied entries included, and the tie visible in the output instead of arbitrarily broken.

Joining through a one-to-many relationship (customer to orders to order_items) means a plain COUNT after the join counts item-rows, not distinct products; COUNT(DISTINCT product_id) is what actually answers "how many different products".

Structured elaboration

SELECT c.customer_id, COUNT(DISTINCT oi.product_id) AS unique_products_count
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY c.customer_id;

Once you join customers to orders to order_items, each customer can appear once per order-item row, not once per customer. A plain COUNT(*) or COUNT(oi.product_id) after this join counts rows in the joined result, which over-counts if the customer bought the same product more than once, or simply reflects total items purchased rather than distinct products. COUNT(DISTINCT product_id) deduplicates within each customer's group, correctly answering the "how many different products" question. Separately, an outer join can introduce NULL product_ids for customers with no purchases at all; COUNT(DISTINCT ...) already ignores NULLs, so this is handled for free.

Worked example

Given one customer with two orders, one order having two order_items (products 5 and 6) and the other having one order_item (product 5 again): COUNT(oi.product_id) returns 3 (three item-rows total), while COUNT(DISTINCT oi.product_id) correctly returns 2 (products 5 and 6, with the repeat of 5 not double-counted).

Now add a second customer with zero orders at all, reached only via the outer join. For that customer, oi.product_id comes back NULL. Side by side: COUNT(*) counts that NULL row anyway, wrongly showing 1 for a customer who has made no real purchases; COUNT(oi.product_id) correctly returns 0, since COUNT of a column never counts NULLs; and COUNT(DISTINCT oi.product_id) also correctly returns 0, for the same reason. So COUNT(*) is the one form that needs the outer join's NULLs actively guarded against (or avoided by counting a non-nullable column instead), while both COUNT(oi.product_id) and COUNT(DISTINCT oi.product_id) handle them safely without any extra work.

Trade-offs and pitfalls

This is a specific instance of a broader pattern: any COUNT taken after a one-to-many join needs to be checked against what grain the join actually produced, not the grain you're mentally picturing (customers, in this case) has become. COUNT(DISTINCT) is the fix here; the more general fix for other aggregate types (SUM, AVG) is pre-aggregating before joining, covered in the double-counting question.