
First-round SQL tests for junior analyst roles are narrower than most people fear. They are rarely about obscure syntax. They test whether you can turn a plain-English business question into a correct query, and whether you notice the traps along the way.
Six patterns cover most of what comes up. The examples below use two simple tables, which is typical of what you will be given:
- customers: customer_id, name, region, signup_date
- orders: order_id, customer_id, order_date, amount
Syntax varies slightly between databases such as PostgreSQL, SQL Server, MySQL and BigQuery. Interviewers rarely mind small differences, as long as your logic is right and you say which dialect you are writing.
1. Aggregate, then filter the groups
“Which customers have placed three or more orders?”
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 3;
The point being tested is the difference between WHERE and HAVING. WHERE filters individual rows before they are grouped. HAVING filters the groups after they have been counted. If the question were “customers with three or more orders over £50”, the £50 condition would go in WHERE and the count would stay in HAVING.
2. Find the rows with no match
“Which customers have never placed an order?”
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
A LEFT JOIN keeps every customer and fills the order columns with NULL where there is no matching order. Filtering on that NULL leaves only the customers with no orders. Using NOT EXISTS is an equally good answer.
Two traps come up often. First, NOT IN behaves unexpectedly if the list it checks contains a NULL, which is why many people prefer NOT EXISTS. Second, adding a condition on the right-hand table to the WHERE clause, such as “o.amount > 50”, quietly turns your LEFT JOIN into an inner join. If you need that condition, put it in the ON clause instead.
3. Rank within a group
“What are the three largest orders in each region?”
WITH ranked AS (
SELECT c.region, o.order_id, o.amount,
ROW_NUMBER() OVER (PARTITION BY c.region ORDER BY o.amount DESC) AS rn
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
)
SELECT region, order_id, amount
FROM ranked
WHERE rn <= 3;
Window functions are the most common step-up question in junior tests. PARTITION BY restarts the numbering for each region. You cannot filter on a window function in the WHERE clause of the same query, which is why the ranking is calculated first in a common table expression (the WITH block).
Expect a follow-up about ties. ROW_NUMBER gives every row a different number, so tied orders are split arbitrarily. RANK gives tied rows the same number and leaves a gap after them. DENSE_RANK gives tied rows the same number with no gap. Ask the interviewer how ties should be handled: asking is part of a good answer.
4. Find duplicates
“Are there duplicate orders in this table?”
SELECT customer_id, order_date, amount, COUNT(*) AS copies
FROM orders
GROUP BY customer_id, order_date, amount
HAVING COUNT(*) > 1;
The real question here is what counts as a duplicate. The order_id will be unique, so you have to decide which columns define “the same order”. Say your assumption out loud. If you are then asked to keep only one copy of each, ROW_NUMBER partitioned by those same columns lets you keep row 1 and drop the rest.
5. Running totals
“Show cumulative revenue by day.”
SELECT order_date,
SUM(daily_revenue) OVER (ORDER BY order_date) AS running_revenue
FROM (
SELECT order_date, SUM(amount) AS daily_revenue
FROM orders
GROUP BY order_date
) AS daily;
Totalling to one row per day first keeps the result clean. If order_date includes a time, convert it to a date before grouping. A window SUM ordered by date then adds up everything up to and including each day. Interviewers sometimes ask about the window frame: writing ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW makes the behaviour explicit and avoids surprises when the ordering column has ties.
6. Compare one period with another
“How does each month’s revenue compare with the same month last year?”
WITH monthly AS (
SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
)
SELECT cur.month, cur.revenue, prev.revenue AS revenue_last_year
FROM monthly cur
LEFT JOIN monthly prev ON prev.month = cur.month - INTERVAL '1 year';
This example is written for PostgreSQL; other databases have their own date functions. Joining the monthly totals to themselves on “the same month a year earlier” works even when some months have no sales. A shortcut using LAG(revenue, 12) is shorter, but it silently gives the wrong answer if any month is missing, and a good interviewer will ask about exactly that.

How to answer, not just what to write
The query is only part of what is being assessed. Strong candidates:
- Clarify first. What counts as a duplicate? How should ties be treated? Do cancelled orders count? Interviewers often leave these gaps on purpose.
- Think about NULLs. COUNT(*) counts rows, COUNT(column) skips NULLs, and a comparison with NULL is never true.
- Check the result. Does the number of rows make sense? Would a customer with no orders appear where they should?
- Talk it through. Explain your approach as you write. A sensible approach with a small syntax slip usually beats perfect code written in silence.
Beyond the six
Other things that come up regularly: CASE expressions to group values into bands (small, medium and large orders, for example), COALESCE to replace NULLs, date functions to group by week or month, and percentages, where integer division can catch you out in some databases.
How to practise
Rather than memorising queries, take a small dataset and write each of the six patterns from a blank screen. Then change the question slightly and do it again. Free practice sites help, but practising on realistic business data, with questions phrased the way a manager would ask them, is closer to the real test.
SQL is a core module of our Data Analytics course, covering filtering, aggregation, joins, CASE, subqueries and window functions on real business questions.