sql-interview-prep

30 SQL interview questions with full solutions, explanations, and alternative approaches. Organized by difficulty, using a realistic e-commerce database schema.

Problem List

Easy (1–10) — Core SQL

#	Title	Concepts
01	Basic SELECT	WHERE, ORDER BY
02	COUNT by Country	GROUP BY, aggregate functions
03	Total Revenue	SUM, AVG, COUNT with WHERE
04	Product Search	Multiple WHERE conditions, BETWEEN
05	Top N Records	ORDER BY DESC, LIMIT
06	Orders Per Customer	INNER JOIN, GROUP BY
07	Products Never Ordered	LEFT JOIN, IS NULL, NOT EXISTS
08	Date Filtering	Date ranges, BETWEEN vs >= and <
09	UPDATE and DELETE	Safe mutations, WHERE importance
10	Aggregations + HAVING	WHERE vs HAVING, aggregate filtering

Medium (11–20) — Joins and Subqueries

#	Title	Concepts
11	Multi-Table JOINs	4-table JOIN, computed columns
12	Self JOIN	Employee hierarchy, LEFT vs INNER
13	GROUP BY with HAVING	Multi-condition HAVING
14	Subquery in WHERE	Scalar subquery, CTE alternative
15	Correlated Subquery	Max per group, performance warning
16	EXISTS / NOT EXISTS	Semi-join, NULL safety vs IN
17	CASE WHEN	Searched vs simple CASE, in aggregates
18	String Functions	UPPER, LOWER, SPLIT_PART, REPLACE
19	Date Functions	EXTRACT, DATE_TRUNC, DATE_PART
20	Derived Table	Subquery in FROM, CROSS JOIN

Hard (21–30) — Window Functions and Advanced

#	Title	Concepts
21	ROW_NUMBER / RANK / DENSE_RANK	Window functions, PARTITION BY
22	LAG / LEAD	Time series, month-over-month growth
23	Running Totals	Window frames, ROWS BETWEEN
24	CTEs	Multi-step analysis, WITH clause
25	Recursive CTE	Org chart traversal, UNION ALL
26	Percentiles	PERCENTILE_CONT, PERCENT_RANK
27	PIVOT / Cross-Tab	Conditional aggregation
28	Gaps and Islands	Consecutive sequences
29	Top N Per Group	ROW_NUMBER + PARTITION BY
30	Cohort Retention	Business analytics, AGE function

Quick Start

SQLite (no install required)

# Run the schema
sqlite3 practice.db < schema/ecommerce.sql

# Run a problem
sqlite3 practice.db < easy/01_basic_select.sql

# Interactive
sqlite3 practice.db

PostgreSQL

# Create database
createdb sql_practice
psql sql_practice < schema/ecommerce.sql

# Run a problem
psql sql_practice < hard/21_window_functions.sql

Docker PostgreSQL

docker run -d --name sql-practice \
  -e POSTGRES_DB=practice \
  -e POSTGRES_PASSWORD=password \
  -p 5432:5432 postgres:15

cat schema/ecommerce.sql | docker exec -i sql-practice \
  psql -U postgres -d practice

Schema Overview

customers (10 rows)
├── orders (15 rows)
│   └── order_items (31 rows)
│       └── products (11 rows)
└── reviews (13 rows)

employee_hierarchy (12 rows) — recursive CTE practice

Key SQL Concepts Covered

Joins: INNER, LEFT, RIGHT, SELF, CROSS JOIN
Aggregations: SUM, COUNT, AVG, MIN, MAX with GROUP BY and HAVING
Subqueries: Scalar, correlated, derived tables, EXISTS/NOT EXISTS
Window Functions: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER, running totals
CTEs: Simple CTEs, multiple CTEs, recursive CTEs
Date/Time: EXTRACT, DATE_TRUNC, AGE, INTERVAL, date arithmetic
String: UPPER, LOWER, SUBSTRING, SPLIT_PART, REPLACE, LIKE
Advanced: CASE WHEN, PIVOT, gaps/islands, cohort analysis, percentiles

Common Interview Tips

1. Always think about NULLs — use IS NULL, not = NULL
2. EXISTS vs IN — EXISTS is safer with NULLs and often faster
3. WHERE vs HAVING — WHERE filters rows, HAVING filters groups
4. Window functions don't collapse rows — unlike GROUP BY
5. CTEs for readability — break complex queries into named steps
6. Index awareness — filter on indexed columns in WHERE/JOIN

License

MIT