SQL Query Fundamentals Questions

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.

Grouping by a computed expression, the substring after '@' in an email column here, works exactly like grouping by any ordinary column; the expression is evaluated per row first, and the GROUP BY then groups on its result, with malformed rows (missing the expected structure) needing an explicit filter to exclude before grouping.

Structured elaboration

SELECT LOWER(SUBSTR(email, INSTR(email, '@') + 1)) AS domain, COUNT(*) AS n
FROM users
WHERE INSTR(email, '@') > 0
GROUP BY domain;

INSTR(email, '@') > 0 filters out any row where the email has no '@' at all (a malformed row that would otherwise make the SUBSTR expression behave unpredictably or return an empty/nonsensical domain); this filter belongs in WHERE, evaluated before the grouping expression runs, not as an afterthought. Speeding this pattern up: a plain B-tree index on the raw email column can't help a query grouping on a computed expression of that column; a functional index built directly on the expression itead (CREATE INDEX ON users (LOWER(SUBSTR(email, INSTR(email,'@')+1))) in engines that support expression indexes) lets the engine avoid recomputing the substring extraction for every row on each query.

Worked example

Given emails 'a@Example.com', 'b@EXAMPLE.com', 'c@other.com', and a malformed 'noatsign' with no '@' at all: the WHERE filter correctly excludes the malformed row before grouping, and the remaining three rows correctly group into example.com (count 2, case-normalized) and other.com (count 1).

Trade-offs and pitfalls

Malformed-row filtering here is a light-touch, single-condition guard appropriate for a quick grouping query; a systematic sweep for many different kinds of malformed email data (missing domain, multiple '@' signs, invalid characters) is a data-quality/validation concern in its own right, belonging to a dedicated data-validation topic rather than being fully solved inline in every query that happens to touch the email column.

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.

A vanilla period-bucketed aggregation buckets rows by a truncated date expression, computes the needed aggregates per bucket, and omits periods with no activity by construction (a GROUP BY simply never produces a row for a bucket that has zero underlying rows).

Structured elaboration

SELECT strftime('%Y-%m', transaction_ts) AS month,
       SUM(amount) AS total_sales, AVG(amount) AS average_amount, COUNT(*) AS order_count
FROM transactions
WHERE transaction_ts >= date('now', '-6 months')
GROUP BY month
ORDER BY month DESC;

The WHERE transaction_ts >= date('now', '-6 months') clause is what actually enforces "the last 6 months" from the question: without it, the query would aggregate every month ever present in the table, silently answering an all-time report instead of a rolling 6-month one. The filter runs before GROUP BY, so months outside the window never even reach the aggregation step.

Because GROUP BY only produces output rows for combinations that actually exist in the data, a month with zero transactions never appears as an explicit "0" row, it's simply absent from the result. Whether that's correct behavior or a bug depends entirely on the consuming report: a sparkline chart that needs every month represented (even as zero) needs a different technique, covered in the zero-fill question; a simple "here's what happened each active month" report is often fine leaving gaps implicit.

Worked example

Given transactions of 100 (January), 50 (January), 200 (February), plus a fourth transaction of 100 from over a year ago, well outside the 6-month window: the WHERE clause excludes that old transaction before grouping even runs, so it contributes to neither the February nor the January row, and the query correctly returns only February (total 200, average 200, count 1) then January (total 150, average 75, count 2), the exact same result it would give without the old transaction in the table at all, and unaffected by its presence.

Trade-offs and pitfalls

This is the base pattern that a large fraction of business-metrics SQL questions are built on top of; recognizing it as one reusable shape (bucket by a truncated date, aggregate per bucket) rather than memorizing dozens of superficially different-looking variants (weekly instead of monthly, revenue instead of counts) is the actual skill being tested.

The same NOT EXISTS/NOT IN and LEFT JOIN...IS NULL patterns apply in the opposite entity direction here (products with no orders, rather than customers with no orders), and the choice between the two approaches carries over identically.

Structured elaboration

-- NOT EXISTS / NOT IN
SELECT p.product_id FROM products p
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.product_id = p.product_id);
-- LEFT JOIN ... IS NULL
SELECT p.product_id FROM products p
LEFT JOIN orders o ON o.product_id = p.product_id
WHERE o.product_id IS NULL;

Both correctly answer "which products have never been ordered". NOT EXISTS tends to read more directly as "there is no order for this product"; LEFT JOIN...IS NULL is sometimes preferred when the query also needs other columns from the (absent) matching row for context, or when a team's SQL style already leans on LEFT JOIN elsewhere for consistency.

Worked example

Given products(1, 2) and orders containing one row referencing product 1: both queries correctly return product 2 as the only never-ordered product.

Trade-offs and pitfalls

Avoid the raw NOT IN (subquery) form unless the orders table's product_id column is guaranteed NOT NULL (many order tables enforce a NOT NULL foreign key on product_id specifically, since an order without a product rarely makes business sense). This is a genuinely narrower risk than the general anti-join caution: on a table keyed to a required entity like product_id, NOT IN is comparatively safer than on a table where the joined-on column can be legitimately NULL, for example a customer_id left NULL by a guest checkout on a customers-to-orders anti-join, where NOT IN's NULL-poisoning risk (one NULL in the subquery's results silently zeroes out the entire result set) is much more plausible to actually hit. Either way, NOT EXISTS sidesteps the question entirely, since it never depends on whether the subquery's column can be NULL.