SQL interview questions for analysts: 12 patterns to drill

SQL interview questions for analysts keep returning to joins, grouping and window functions, and to one harder test: do you notice when a query that runs gives the wrong number? No public study counts these questions by topic, so the twelve patterns below are a practice list, not a measurement. Work through them and you can write and explain each one on a public dataset.

This is part 3 of "The analyst's stack". If you're still moving out of Excel reporting, start with the 16-week path from Excel to data analyst.

What SQL interview questions for analysts actually test

The evidence is thinner than most prep guides suggest, so here is what each piece can carry.

• Usage. SQL was used by 58.4% of respondents to Stack Overflow's 2026 survey, which drew 30,903 responses ¹. Those are developers and technologists, not analysts.
• Job listings. One Amazon Business Intelligence Engineer II listing, covering Bengaluru, Hyderabad and Gurugram, asks for "using SQL to pull data from a database or data warehouse", with Python. The same role builds automated reporting and checks production performance ². It's one employer's listing, not a market count.
• Skills reports. LinkedIn, which sells hiring products, says querying and database optimisation are "gaining importance" in India, without stating a detailed method ³.
• Interview reports. Ian Klosowicz, who sells an analyst programme, names grouping, joins and window functions as the three concepts analyst interviews test. He gives anecdotes, not data ⁴.

The market itself is mixed. Indeed, which sells hiring services, was reported in September 2026 as showing India's data and analytics postings down 14% ⁵. That counts postings on one platform and says nothing about what gets asked.

Twelve patterns, on one public dataset

All twelve use Chinook, a free sample database of a digital media store. It ships for SQLite, MySQL, PostgreSQL and SQL Server, among others, and its sales data is randomly generated ⁶. The last release is from February 2024, which doesn't matter for practice. The queries below are written for SQLite; names and date functions differ by build, so adjust them to yours.

1. Group and filter the groups. Countries with more than four customers.

SELECT Country, COUNT(*) AS customers
FROM Customer GROUP BY Country
HAVING COUNT(*) > 4;

WHERE runs before GROUP BY and HAVING after it, and an alias from SELECT isn't available to earlier steps ⁷.

2. Join across tables. Revenue by genre.

SELECT g.Name, SUM(il.UnitPrice * il.Quantity) AS revenue
FROM InvoiceLine il
JOIN Track t ON t.TrackId = il.TrackId
JOIN Genre g ON g.GenreId = t.GenreId
GROUP BY g.Name;

3. Spot the fan-out. This query overstates revenue, because each invoice repeats once per line.

SELECT SUM(i.Total) FROM Invoice i
JOIN InvoiceLine il ON il.InvoiceId = i.InvoiceId;

Say what one row means before you sum anything. Here the fix is SELECT SUM(Total) FROM Invoice.

4. Find what's missing. Tracks never sold.

SELECT t.TrackId, t.Name FROM Track t
LEFT JOIN InvoiceLine il ON il.TrackId = t.TrackId
WHERE il.TrackId IS NULL;

5. ON versus WHERE. Every customer, with their count of invoices above 10.

SELECT c.CustomerId, COUNT(i.InvoiceId) AS big_invoices
FROM Customer c
LEFT JOIN Invoice i
  ON i.CustomerId = c.CustomerId AND i.Total > 10
GROUP BY c.CustomerId;

Move the filter to WHERE and customers with none disappear. COUNT(*) would also give them 1.

6. Conditional totals. USA against all revenue, by year.

SELECT strftime('%Y', InvoiceDate) AS yr,
  SUM(CASE WHEN BillingCountry = 'USA' THEN Total ELSE 0 END) AS usa,
  SUM(Total) AS all_revenue
FROM Invoice GROUP BY yr;

7. Handle NULL. How many customers have a company on record?

SELECT COUNT(*) AS total_rows, COUNT(Company) AS with_company
FROM Customer;

To filter, use IS NULL. A comparison such as = NULL returns NULL, not true, and two NULLs count as equal in GROUP BY ⁸.

8. Latest row per group. Each customer's most recent invoice.

WITH ranked AS (
  SELECT CustomerId, InvoiceId, InvoiceDate,
    ROW_NUMBER() OVER (PARTITION BY CustomerId
      ORDER BY InvoiceDate DESC, InvoiceId DESC) AS rn
  FROM Invoice)
SELECT * FROM ranked WHERE rn = 1;

The CTE is required. Window functions are allowed only in the select list and ORDER BY, because they run after WHERE, GROUP BY and HAVING ⁹¹¹.

9. Top N per group, with ties. Top three tracks by revenue in each genre.

WITH sales AS (
  SELECT t.GenreId, t.TrackId, t.Name,
    SUM(il.UnitPrice * il.Quantity) AS revenue
  FROM InvoiceLine il JOIN Track t ON t.TrackId = il.TrackId
  GROUP BY t.GenreId, t.TrackId, t.Name),
ranked AS (
  SELECT *, DENSE_RANK() OVER (PARTITION BY GenreId
    ORDER BY revenue DESC) AS rnk
  FROM sales)
SELECT * FROM ranked WHERE rnk <= 3;

Expect to explain the choice. On tied values ROW_NUMBER keeps counting, RANK leaves gaps and DENSE_RANK doesn't. Microsoft's reference page, dated 2017, shows all three side by side ¹⁰.

10. Compare with the previous period. Month-on-month change.

WITH m AS (
  SELECT strftime('%Y-%m', InvoiceDate) AS month, SUM(Total) AS revenue
  FROM Invoice GROUP BY month)
SELECT month, revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM m;

LAG reads the previous row, not the previous calendar month. A month with no sales breaks the comparison, so mention it.

11. Running total and share. On the same m CTE:

SELECT month,
  SUM(revenue) OVER (ORDER BY month) AS running_total,
  revenue * 1.0 / SUM(revenue) OVER () AS share
FROM m;

Without the * 1.0, some engines divide integers and return zero.

12. Join a table to itself. Each employee and their manager.

SELECT e.LastName, m.LastName AS manager
FROM Employee e
LEFT JOIN Employee m ON m.EmployeeId = e.ReportsTo;

An inner join would drop whoever has no manager.

Where the course fits

Patterns	Skill at work	SQL Expert module
1–7, 12	Pulling a correct number for the Monday report	SQL Foundations
8–11	Trend, ranking and period reports	SQL Advanced
Checking drafts	Testing an assistant's query before it reaches the deck	Generative AI for Analysts

TTFA's SQL Expert course also covers execution plans and indexing, the follow-up topics when a query runs slowly on a large table.

Checking what an assistant writes

An assistant can draft all twelve, and you still own the number. In the Spider 2.0 paper, an o1-preview agent solved 21.3% of real-world enterprise tasks, against 91.2% on the simpler Spider 1.0 ¹². That paper dates from March 2025, and models have moved since. The gap between tidy and messy schemas is the point.

Check a draft the way an interviewer checks you: state what one row means, compare row counts before and after each join, and test one total by hand.

What we don't know

There's no public count of how often each pattern appears, and interviews differ by employer and level. Twelve is this note's grouping for practice, so it doesn't promise coverage of any particular round.

What to do with this: load Chinook, write each query without looking, then explain out loud what one row means. Check which database the listing names too. PostgreSQL, MySQL and SQL Server are all common, at 57.9%, 35.1% and 27.1% of Stack Overflow's respondents ¹, and date functions differ.

You can now write and defend twelve query patterns. The next note in the series, "What the PL-300 exam actually tests, and how working analysts prepare", moves up to the BI tool: what the exam covers and how people with full-time jobs prepare for it.

Frequently asked questions

Which SQL should I practise first?

Start with patterns 1 to 5, since a wrong join or filter breaks everything built on it. Then move to window functions, which Klosowicz names alongside grouping and joins ⁴.

Does it matter whether I use MySQL or SQL Server?

The patterns carry across, but syntax such as date functions doesn't. Practise in the database your target listing names.

Are window functions needed for analyst roles?

Plan on it. They're the standard way to answer latest-per-group, ranking and period questions, and the documentation for both PostgreSQL and MySQL covers them ⁹¹¹.