Databases & SQL Interview Questions: Querying & Aggregation
Reviewed by Mark Dickie · Last updated
SQL aggregation is the process of computing summary values across groups of rows using functions like SUM, COUNT, AVG, MIN, and MAX. For interview purposes, the core skills are writing GROUP BY queries, understanding how HAVING differs from WHERE, choosing the right join, and reasoning about how rows flow through a query before aggregation happens. Most candidates stumble when the question mixes a self-join with a window function or asks for a top-N-per-group result, so focus your prep on those patterns.
The table below maps the question categories you should expect, ordered roughly by how often they appear in phone screens and take-homes:
| Category | What it tests | Typical prompt shape |
|---|---|---|
| Filtering & projection | WHERE, CASE, column selection | "Return all orders above $500 placed last month." |
| Joins | Inner, left, self-joins | "Find employees who manage at least one direct report." |
| Grouping & aggregation | GROUP BY, HAVING, aggregate functions | "What is the average order value per customer?" |
| Window functions | RANK, ROW_NUMBER, running totals | "Rank products by revenue within each category." |
| Top-N per group | Window + partition + filter | "Get the three most recent orders per user." |
What does a SQL aggregation interview question actually test?
Interviewers want to see whether you can break a problem into stages: filter the raw rows first, group them correctly, apply the aggregate, then sort or limit. They also check that you know the execution order of clauses, because getting it wrong means the query either errors out or silently returns wrong numbers.
How should you prepare for database querying interviews?
- Drill
GROUP BYwith multi-column keys until it is automatic — most aggregation questions group by more than one column. - Memorize the logical processing order (
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY) and practice explaining it out loud. - Write five to ten window-function queries by hand, especially
RANK()andROW_NUMBER()withPARTITION BY, since whiteboard questions favor them. - Practice self-joins on employee–manager or friend–friend schemas; they appear constantly and trip people up.
- Time yourself on top-N-per-group problems — this is the single pattern that separates mid-level from senior candidates in SQL rounds.
Work through the quiz below to check where you stand on each of these areas.
Key facts
- Tarmac's Databases & SQL interview questions cover 10 questions at difficulty 2–4 of 5.
- Tarmac tracked 8,648 job postings asking for Databases & SQL in August 2026.
- Roles asking for Databases & SQL advertise a median base salary of US$166,400, across 1,383 job postings as of August 2026.
- Tarmac last reviewed these Databases & SQL interview questions on 31 August 2026.
At a glance
| Questions | 10 |
|---|---|
| Difficulty | 2–4 of 5 |
| Formats | True / false, Coding exercise, Multiple choice, Code output, Find the bug, Short answer |
| Interactive | 4 run your code against tests, in the app |
What you'll review
- window functions
- aggregation
Practice questions
Try one before you open the answer. Pick an option and press Check; it's marked on the spot.
Databases & SQL/querying/window-functions
Unlike GROUP BY aggregation, a window function returns one output row for every input row, preserving all original rows in the result.#
Options
Show answer
True. A window function returns one output row for every input row, preserving all original rows, whereas GROUP BY collapses multiple rows into one summary per group and discards the detail. This row-preserving behaviour is why window functions let you show both row-level detail and an aggregate, such as each employee's salary beside the department average, without a subquery.
GROUP BY collapses multiple rows into one summary row per group, discarding the original row detail. A window function computes its result alongside each row without collapsing them — every input row appears in the output, augmented with the computed window value. This is why window functions are used when you need both row-level detail and an aggregate value (e.g. each employee's salary next to the department average) without a subquery.
Databases & SQL/querying/aggregation
The users table has columns id (INTEGER) and email (TEXT). Write a query returning each email address that appears more than once, with columns email and cnt (how many times it appears), ordered by cnt descending, then email ascending.#
Starter code
-- Return: email, cnt (count > 1), ordered by cnt DESC, email ASC
SELECT
Your solution must pass
- finds both duplicated emails with their counts
This one is written and run, not read. Solve it in the app and your code is executed against these tests and the hidden ones.
Databases & SQL/querying/aggregation
A table has 10 rows; the manager_id column is NULL in 3 of them. How do COUNT(*) and COUNT(manager_id) differ?#
Options
Show answer
COUNT(*) returns 10 and COUNT(manager_id) returns 7. COUNT(*) counts every row regardless of nulls, while COUNT(manager_id) counts only rows where that column is non-null, skipping the 3 nulls. Every standard aggregate except COUNT(*) ignores NULL inputs.
COUNT(*) counts rows regardless of nulls, so it returns 10. COUNT(manager_id) counts only rows where that expression is non-null, skipping the 3 nulls to return 7. Every standard aggregate except COUNT(*) ignores NULL inputs.
Databases & SQL/querying/aggregation
sales(region, amount) has (East, 100), (East, 100), (West, 100), (North, 400). What does this query return?#
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region
HAVING SUM(amount) > 150
ORDER BY total DESC;Options
Show answer
(North, 400), (East, 200)
GROUP BY region produces East=200, West=100, North=400. HAVING filters on the aggregate, keeping only groups whose SUM(amount) > 150, which drops West (100). ORDER BY total DESC then yields (North, 400), (East, 200). HAVING filters groups after aggregation, whereas WHERE would filter individual rows before it.
Databases & SQL/querying/aggregation
The orders table has columns id (INTEGER) and ordered_at (TEXT, 'YYYY-MM-DD'). Write a query that buckets orders by calendar week using strftime('%Y-%W', ordered_at) and counts how many orders fall in each week. Return columns week (e.g. '2024-01') and order_count, ordered by week ascending.#
Starter code
-- Bucket with strftime('%Y-%W', ordered_at), then count per bucket
-- Return: week, order_count, ordered by week ASC
SELECT
Your solution must pass
- three consecutive January weeks with 2, 2, and 3 orders
This one is written and run, not read. Solve it in the app and your code is executed against these tests and the hidden ones.
Databases & SQL/querying/aggregation
The customers table has columns id (INTEGER) and name (TEXT). The orders table has columns id (INTEGER), customer_id (INTEGER referencing customers.id), and total (INTEGER). Write a query returning each customer whose orders add up to STRICTLY more than 250, with columns name and total_spent (the sum of their order totals), ordered by total_spent descending, then name ascending.#
Starter code
-- Join customers to orders, group per customer, keep groups with SUM(total) > 250.
-- Return: name, total_spent, ordered by total_spent DESC then name ASC.
SELECT
Your solution must pass
- two customers clear the spend threshold, biggest spender first
This one is written and run, not read. Solve it in the app and your code is executed against these tests and the hidden ones.
Databases & SQL/querying/aggregation
Under standard SQL (and PostgreSQL), this query is rejected with a grouping error. What is wrong?#
SELECT department, employee_name, COUNT(*)
FROM employees
GROUP BY department;Options
Show answer
employee_name is in the SELECT list but is neither aggregated nor in the GROUP BY
When you GROUP BY department, each output row represents many employees, so a bare employee_name is ambiguous — which employee's name? Standard SQL requires every non-aggregated SELECT column to appear in GROUP BY. Either add employee_name to the GROUP BY or wrap it in an aggregate such as MAX(employee_name).
Databases & SQL/querying/window-functions
Both GROUP BY and a window function like AVG(salary) OVER (...) can compute an average per group. What is the key difference in what each returns?#
Show answer
GROUP BY collapses each group down to a single output row, so you lose the individual rows and can only emit aggregates and the grouping columns. A window function computes the aggregate over a partition but keeps every input row, attaching the group-level value to each detail row alongside its own columns. So if you want each employee's row to also show their department average, you use a window function; GROUP BY would give you one row per department instead.
GROUP BY reduces N rows in a group to one summary row, discarding the detail. A window function (OVER (...)) evaluates the same aggregate across a partition but does not collapse rows — every original row survives and gets the computed value appended. That row-preserving behaviour is exactly why window functions are reached for when you need both the detail and a group-level metric (average, total, rank) in the same result set.
Databases & SQL/querying/aggregation
The sales table has columns id (INTEGER), product (TEXT), channel (TEXT, always 'online' or 'store'), and amount (INTEGER). Write a query that pivots the channels into columns: one row per product with columns product, online_sales (sum of amounts where channel is 'online'), and store_sales (sum where channel is 'store'). A product with no sales in a channel must show 0 (not NULL) for that column. Order by product ascending. Use conditional aggregation: SUM(CASE WHEN ... THEN amount ELSE 0 END).#
Starter code
-- One row per product; pivot channel into online_sales / store_sales columns
-- with SUM(CASE WHEN ... THEN amount ELSE 0 END). Order by product ASC.
SELECT
Your solution must pass
- channels pivot to columns; missing channels show 0
This one is written and run, not read. Solve it in the app and your code is executed against these tests and the hidden ones.
Databases & SQL/querying/window-functions
The developer wants the total sales per region alongside each sale's percentage of that region's total. The query errors on execution. What is the bug?#
SELECT region, sale_id, amount,
SUM(amount) OVER (PARTITION BY region) AS region_total,
amount / SUM(amount) OVER (PARTITION BY region) * 100 AS pct
FROM sales
GROUP BY region;Options
Show answer
GROUP BY region collapses rows before the window function can see individual sale_id and amount values, making the window function invalid
GROUP BY region reduces the result to one row per region before window functions run. At that point sale_id and amount are no longer meaningful individual values, so the database rejects them in the SELECT list (they are neither in the GROUP BY nor wrapped in an aggregate). Remove the GROUP BY entirely — window functions compute aggregates across rows without collapsing them, so SUM(amount) OVER (PARTITION BY region) already gives each row the region total while keeping every sale row intact.
Sources
The official documentation these questions are checked against:
Related interview questions
Job market
See databases-sql salaries and hiring demand from live job postings.
Practise these until they stick
That's every question we hold on this topic, and the page marks what you pick. What it can't do is remember. A free account keeps every answer, and what you miss comes back until it's right: after a day, then at longer gaps.
Free · the whole bank · 100 marked answers per 30 days · written feedback on the paid plan