Skip to content
Tech Interview Prep
Technical interview guide

Aggregations & GROUP BY

Collapsing many rows into one summary row per group (counts, sums, and averages) plus the HAVING clause that filters groups.

Read
22 min
Practice MCQs
25
Interview Q&A
25
Edition
v3
Editorial status
Reviewed

Scope: SQL principles with PostgreSQL 18 examples; vendor-specific behavior must be verified.

Curated: · Written: · Reviewed:

25 questions

MCQ-4A table `events(id, region)` holds 9 rows: 3 with region 'us', 2 with 'eu', and 4 with region NULL. On PostgreSQL, MySQL and SQL Server, what does `SELECT region, COUNT(*) FROM events GROUP BY region;` return?
MCQ-11A table `orders` has 6 rows. The `customer_id` values are: 1, 1, 2, NULL, 3, 3. A query runs `SELECT COUNT(*) AS total_rows, COUNT(DISTINCT customer_id) AS unique_customers FROM orders;` What does it return?
MCQ-8A quiz table `attempts(score)` holds one row per student: 80, 90, NULL, 70, NULL, 100, where NULL means the student skipped. Two queries run against it: P is `SELECT AVG(score) FROM attempts;` and Q is `SELECT SUM(score)/COUNT(*) FROM attempts;`. Which pair gives the results of P and Q?
MCQ-1A `payments` table with 4.2 million rows has columns `id`, `account_id`, `status`, `amount_cents`. `status` takes exactly three distinct values across the table: `'settled'`, `'pending'` and `'reversed'`. A nightly job runs: ```sql SELECT COUNT(*), SUM(amount_cents) FROM payments WHERE status = 'reversed'; ``` No row in the table currently has `status = 'reversed'`. What does the job's query return?
MCQ-3A dashboard runs this query for account 8842: SELECT SUM(amount) AS total FROM payments WHERE account_id = 8842 AND status = 'settled'; Account 8842 has three pending payments totaling 120.00 and no settled payments. The team wants the total column to show 0 for accounts with no settled payments, while still summing amounts when settled payments exist. Which change achieves that?
MCQ-2A query `SELECT dept, COUNT(*) FROM emp WHERE COUNT(*) > 3 GROUP BY dept` is rejected before execution, while the same predicate in HAVING runs. Which statement explains the rejection?
MCQ-13A `payments` table holds one row per payment: | account | status | amount | |---------|--------|--------| | A | settled | 40 | | A | settled | 70 | | A | pending | 500 | | B | settled | 30 | | B | pending | 900 | | C | settled | 150 | An analyst runs: ```sql SELECT account, SUM(amount) AS total FROM payments WHERE status = 'settled' GROUP BY account HAVING SUM(amount) >= 100; ``` Which result does this query return?
MCQ-7A nightly report runs this query on PostgreSQL 16 over a 40M-row `orders` table and takes 90 s; EXPLAIN shows the HAVING filter evaluated after the hash aggregate: ```sql SELECT region, COUNT(*) AS orders, SUM(amount) AS revenue FROM orders WHERE created_at >= DATE '2024-01-01' GROUP BY region HAVING COUNT(*) >= 50 AND region <> 'test' AND SUM(amount) > 10000 AND COUNT(*) FILTER (WHERE status = 'cancelled') > 0 AND MAX(created_at) >= DATE '2024-06-01'; ``` Which HAVING predicate can be moved to WHERE to cut the rows entering the aggregate?
MCQ-15A support team tracks tickets in `tickets(agent, priority)` on PostgreSQL 16. The rows are: | agent | priority | | --- | --- | | ana | low | | ana | high | | ana | high | | ben | low | | ben | NULL | | cleo | high | A developer wants the number of high-priority tickets per agent and writes: ```sql SELECT agent, COUNT(CASE WHEN priority = 'high' THEN 1 ELSE 0 END) AS high_count FROM tickets GROUP BY agent; ``` What does this query return?
MCQ-5A per-customer report over a 400M-row `orders` table must return one row per customer that has at least one order, with the count of cancelled orders and the count of shipped orders as two columns, from a single scan. Which query does that?
MCQ-6A revenue report sums `orders.total`, grouped by region. After adding a join to `order_items`, one region's total rose from 300 to 600. That region has 3 orders and 6 item rows. What caused the increase?
MCQ-24`orders(order_id, customer_id, total_cents)` has one row per order. `order_items(order_id, price_cents)` has three rows per order. Customer 7 has orders 101 (`total_cents = 1000`) and 102 (`total_cents = 2000`). A developer runs: ```sql SELECT DISTINCT o.customer_id, SUM(o.total_cents) AS revenue FROM orders o JOIN order_items i ON i.order_id = o.order_id WHERE o.customer_id = 7 GROUP BY o.customer_id; ``` What does this query return for customer 7, and what does that show about using DISTINCT to fix the duplicated aggregate?
MCQ-18`customers` holds 5 rows (ids 1–5). `orders` holds 7 rows whose `customer_id` values are 1, 1, 1, 2, 2, 3, and NULL for a guest checkout; customers 4 and 5 have never ordered. A report needs the number of customers who have placed at least one order, and an engineer writes: ```sql SELECT COUNT(DISTINCT o.customer_id) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id; ``` What number does this query return?
MCQ-22Table `t(cid, status, amt)` holds one row per order. `amt` is NOT NULL and no two rows share an `amt`. Customer 3 has only `status = 'pending'` rows; customers 1 and 2 each have at least one `'paid'` row. Which pair of queries returns a different number of rows?
MCQ-12An orders table has one row per order, with a customer_id and an amount. A team wants the average customer total: the mean, across all customers, of each customer's SUM(amount). Which query returns that figure?
MCQ-10`payments(customer_id, payment_id, amount)` holds five rows: customer 7 has payments of 40, 25 and 35; customer 9 has payments of 60 and 90. Two queries run against it: ```sql -- Q1 SELECT customer_id, SUM(amount) AS customer_total FROM payments GROUP BY customer_id; -- Q2 SELECT customer_id, payment_id, SUM(amount) OVER (PARTITION BY customer_id) AS customer_total FROM payments; ``` Which pair of results do the two queries actually produce?
MCQ-20PostgreSQL 16. A query computes `row_number() OVER (PARTITION BY customer_id ORDER BY placed_at DESC) AS rn` in its SELECT list, and the team needs only the rows where `rn = 1`. Where must that filter be placed?
MCQ-14`orders` has columns `order_id` (primary key), `customer_id`, `region`, `total`. In the current data every `customer_id` maps to exactly one `region`. A team runs this query unchanged on Postgres 16 and on MySQL 8.0 with default settings: ```sql SELECT customer_id, region, SUM(total) FROM orders GROUP BY customer_id; ``` What happens?
MCQ-9A table sales(region, product, amount) contains ('East','Widget',100), ('East','Gadget',50), ('West','Widget',200) and ('West',NULL,30), where a NULL product marks an unclassified sale. An analyst runs: SELECT region, product, SUM(amount) AS total FROM sales GROUP BY ROLLUP(region, product); Which statement about the result is correct?
MCQ-19A `sales(region, product, amount)` table holds four rows: `('east','widget',10)`, `('east','gadget',20)`, `(NULL,'widget',5)`, a genuinely unknown region, and `('west','widget',7)`. The analyst runs: ```sql SELECT region, product, SUM(amount) AS total, GROUPING(region) AS g_region FROM sales GROUP BY ROLLUP(region, product); ``` and wants to isolate the grand-total row from the rest of the report. Which predicate, applied to that result, returns only the grand total?
MCQ-16A nightly report runs `SELECT user_id, count(*) FROM events GROUP BY user_id`. On the 200k-row staging table it returns in 40 ms; on the 40M-row production table it takes 6 s. `EXPLAIN (ANALYZE, BUFFERS)` shows the same plan shape in both, one HashAggregate over a Seq Scan, with 99% shared-buffer hits in production, and the production HashAggregate node reports `Batches: 512` and `Disk Usage: 1180 MB`. `work_mem` is left at its 4 MB default. Which cause does that evidence single out?
MCQ-21`orders` holds 40 M rows. `orders_cust_created_idx` is a B-tree on `(customer_id, created_at)`. On a test instance, `SELECT MAX(created_at) FROM orders WHERE customer_id = 42;` returns in 0.05 ms reading 3 buffers, while `SELECT MAX(created_at) FROM orders;` takes 3.8 s and reads all 40 M rows. What explains the difference?
MCQ-17A dashboard must show data no older than 5 minutes. The team replaces a slow plain view with a materialized view and schedules a full refresh every 5 minutes; each refresh takes 6 s. What is the worst-case age of the data a reader can see?
MCQ-25A 1.2 TB events table holds 800 million rows. A dashboard aggregates the last 90 days by day and region; the query scans most of the table, takes 40 s, and runs about 3,000 times a day. Rows older than 7 days never change, while the last 7 days take roughly 2 million new rows a day. A full rebuild of a 90-day rollup table takes 11 minutes. The dashboard must be no more than 5 minutes stale, and 900 analysts each view their own filter combination. Which approach meets the freshness bound and removes the repeated 40 s scans?
MCQ-23A nightly data-quality check runs on an `events` table with 1,200 rows. It records `COUNT(*) = 1200`, `COUNT(user_id) = 1200`, `COUNT(device_id) = 1140`, and `COUNT(referrer) = 900`. The alert is configured to fire when any column is NULL in more than 5% of rows. Which conclusion follows?