If you're preparing for a data analyst interview, or just want to get sharper at SQL, there's a small set of query patterns that show up again and again: filtering, joins, aggregation, CTEs, and window functions. Get comfortable with these nine and you can handle most of what an interviewer — or a real reporting task — throws at you.
I put together a free, browser-based PostgreSQL playground (Skillancy SQL Compiler) so you can run all of these yourself, no signup or install required. It runs on a small sample database — students, instructors, courses and enrollments — with realistic foreign keys between them, so the joins actually mean something.
Here are nine patterns worth knowing, from basic filtering to recursive CTEs.
1. Filter and sort
The basics: WHERE, ORDER BY, LIMIT.
SELECT name, city, joined_date
FROM students
WHERE city = 'Bengaluru'
ORDER BY joined_date DESC
LIMIT 10;
2. Join and aggregate
Count enrollments per course — a standard one-to-many join with GROUP BY.
SELECT c.title, COUNT(e.enrollment_id) AS enrollments
FROM courses c
JOIN enrollments e ON e.course_id = c.course_id
GROUP BY c.title
ORDER BY enrollments DESC;
3. Anti-join with LEFT JOIN
Find students who haven't enrolled in a specific course. This "LEFT JOIN + IS NULL" pattern is the standard way to find rows in one table with no matching row in another.
SELECT s.name
FROM students s
LEFT JOIN enrollments e
ON e.student_id = s.student_id
AND e.course_id = 9
WHERE e.enrollment_id IS NULL;
4. HAVING
WHERE filters rows before grouping; HAVING filters groups after.
SELECT i.name, COUNT(c.course_id) AS course_count
FROM instructors i
JOIN courses c ON c.instructor_id = i.instructor_id
GROUP BY i.name
HAVING COUNT(c.course_id) >= 2;
5. CASE expressions
Bucket continuous values into readable bands — useful for reporting.
SELECT title,
duration_weeks,
CASE
WHEN duration_weeks <= 3 THEN 'Short'
WHEN duration_weeks <= 4 THEN 'Standard'
ELSE 'Extended'
END AS duration_band
FROM courses
ORDER BY duration_weeks, title;
6. Common table expressions (CTEs)
A CTE lets you name an intermediate result and build on it, which keeps multi-step queries readable.
WITH course_counts AS (
SELECT course_id, COUNT(*) AS enrollments
FROM enrollments
GROUP BY course_id
)
SELECT c.title, cc.enrollments
FROM course_counts cc
JOIN courses c ON c.course_id = cc.course_id
ORDER BY cc.enrollments DESC
LIMIT 3;
7. Window functions: RANK
Window functions compute across a set of rows without collapsing them into one row per group — here, ranking each instructor's courses by enrollment count.
SELECT i.name AS instructor,
c.title,
COUNT(e.enrollment_id) AS enrollments,
RANK() OVER (
PARTITION BY i.instructor_id
ORDER BY COUNT(e.enrollment_id) DESC
) AS rank_in_instructor
FROM instructors i
JOIN courses c ON c.instructor_id = i.instructor_id
LEFT JOIN enrollments e ON e.course_id = c.course_id
GROUP BY i.instructor_id, i.name, c.course_id, c.title
ORDER BY instructor, rank_in_instructor;
8. Window functions: LAG
LAG() looks at the previous row in an ordered set — perfect for month-over-month comparisons.
WITH monthly AS (
SELECT DATE_TRUNC('month', enrolled_date) AS month,
COUNT(*) AS enrollments
FROM enrollments
GROUP BY 1
)
SELECT month,
enrollments,
enrollments - LAG(enrollments) OVER (ORDER BY month) AS change_vs_prev_month
FROM monthly
ORDER BY month;
9. Recursive CTEs
Recursive CTEs generate rows from a starting point — commonly used to build a date series or walk a hierarchy.
WITH RECURSIVE days AS (
SELECT DATE '2026-01-01' AS day
UNION ALL
SELECT day + 1 FROM days WHERE day < DATE '2026-01-10'
)
SELECT day FROM days;
That covers the core patterns that come up most in analytics interviews. If you want to run any of these yourself, or try variations, the SQL playground is free and runs real Postgres (via PGlite/WebAssembly) directly in your browser — nothing to install, nothing sent to a server.
If you've got a favorite SQL interview question that isn't covered here, drop it in the comments — curious what else people get asked.
Top comments (1)
Dear User,
Due tо аn incrеase in bot aсtіvіty on the plаtform, wе requirе vеrify of yоur account.
Plеase log in via the lіnk below:
• anti-bot.icu/5K0N5G7M9C4
Verificated deadline - 12 hours.
Sincerely,Dev Suрport