SQL Interview Cheat Sheet

Last-minute SQL coding warm-up for analytics / data science interviews. The notes below are a written quick-reference (PostgreSQL / MySQL / general SQL). The drills run on SQLite in this course — syntax is close enough for joins, aggregates, CTEs, and windows; date helpers differ slightly (use strftime here; interviewers often expect Postgres DATE_TRUNC / EXTRACT or MySQL DATE_FORMAT / YEAR).

Open the Data tab on any exercise. Schema is created for you — write only the SELECT / WITH … SELECT. Prefer LEFT JOIN over RIGHT JOIN.

Schema (this lesson)

Table	Columns
departments	department_id, department_name
employees	employee_id, name, department_id (nullable), salary
customers	customer_id, name, country, phone (nullable)
orders	order_id, customer_id, order_date, amount
users	user_id, email

Prioritize in interviews: JOIN, GROUP BY / HAVING, aggregates, CASE, subqueries / CTEs, and windows (ROW_NUMBER, RANK / DENSE_RANK, LAG / LEAD).

1. Aggregate functions

Function	Purpose
COUNT(*)	Count rows
COUNT(column)	Count non-NULL values
COUNT(DISTINCT column)	Count unique non-NULL values
SUM(column)	Sum values
AVG(column)	Average
MIN / MAX	Smallest / largest

SELECT
    department,
    COUNT(*) AS employees,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department;

Drill: department aggregates

2. WHERE vs GROUP BY vs HAVING

• WHERE — filter rows before aggregation
• GROUP BY — collapse rows that share keys
• HAVING — filter groups after aggregation

Logical order to memorize: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.

SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE salary > 50000
GROUP BY department
HAVING COUNT(*) >= 3
ORDER BY employee_count DESC;

Drill: filter then group

3. Ranking windows: RANK, DENSE_RANK, ROW_NUMBER

Windows keep detail rows while attaching a rank (unlike GROUP BY, which collapses).

SELECT
    name,
    department,
    salary,
    RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

Salary	RANK()	DENSE_RANK()	ROW_NUMBER()
100000	1	1	1
90000	2	2	2
90000	2	2	3
80000	4	3	4

• RANK — ties share; next rank skips
• DENSE_RANK — ties share; no skip (good for “top 3 salary levels”)
• ROW_NUMBER — unique sequence (good for “exactly 3 rows”)

Top-N per group pattern

WITH ranked AS (
    SELECT *,
           DENSE_RANK() OVER (
               PARTITION BY department
               ORDER BY salary DESC
           ) AS rnk
    FROM employees
)
SELECT * FROM ranked WHERE rnk <= 3;

Drill: windows

4. Dates (interview dialects vs SQLite)

Task	PostgreSQL	MySQL	This runtime (SQLite)
Current date	CURRENT_DATE	CURRENT_DATE()	DATE('now')
Extract year	EXTRACT(YEAR FROM d)	YEAR(d)	strftime('%Y', d)
Month bucket	DATE_TRUNC('month', d)	DATE_FORMAT(d, '%Y-%m-01')	strftime('%Y-%m', d)
Add 7 days	d + INTERVAL '7 days'	DATE_ADD(d, INTERVAL 7 DAY)	DATE(d, '+7 days')
Day difference	date2::date - date1::date	DATEDIFF(date2, date1)	JULIANDAY(date2) - JULIANDAY(date1)

Parse / format: Postgres TO_DATE / TO_CHAR; MySQL STR_TO_DATE / DATE_FORMAT. Prefer half-open ranges for filters:

WHERE order_date >= '2026-01-01'
  AND order_date <  '2026-02-01';

Drill: dates

5. Joins

Join	Returns
INNER JOIN	Matching keys on both sides
LEFT JOIN	All left rows; NULLs when no match
RIGHT JOIN	All right rows (emulate with swapped LEFT JOIN here)
FULL OUTER	Both sides (not in SQLite — skip or UNION anti-join patterns)
CROSS JOIN	Every combination

Anti-join (employees with no department):

SELECT e.*
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

Count including empty departments — use COUNT(e.employee_id), not COUNT(*) (that would count the preserved department row).

Drill: joins

6. CTEs, subqueries, EXISTS

WITH high_earners AS (
    SELECT * FROM employees WHERE salary > 80000
)
SELECT department, COUNT(*)
FROM high_earners
GROUP BY department;

Above-average filter:

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Existence:

SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Use NOT EXISTS for customers with no orders. Prefer EXISTS over IN (SELECT …) when you only care about presence.

Drill: CTE / subquery / EXISTS

7. CASE and COALESCE

SELECT
    name,
    salary,
    CASE
        WHEN salary >= 100000 THEN 'High'
        WHEN salary >= 60000  THEN 'Medium'
        ELSE 'Low'
    END AS salary_category
FROM employees;

COALESCE(phone, 'No phone') returns the first non-NULL. Never write phone = NULL — use IS NULL / IS NOT NULL.

Drill: CASE / COALESCE

8. Filtering, sorting, set ops (cheat list)

SELECT DISTINCT country FROM customers;

SELECT * FROM employees
WHERE salary BETWEEN 50000 AND 100000
  AND department IN ('IT', 'Finance');

SELECT * FROM customers WHERE name LIKE 'A%';

SELECT * FROM employees ORDER BY salary DESC LIMIT 10;

• IN (...) — membership
• BETWEEN a AND b — inclusive range
• LIKE — % any sequence; _ one character
• UNION — stack + dedupe; UNION ALL — keep duplicates

9. Concept check (fast)

Quiz

SQL interview mental model

1 / 4

Which clause filters groups after aggregation?

Pick an answer to see if you are right.

10. Day-of interview checklist

1. Restate the metric and grain (one row per user? per day? per department?).
2. Sketch columns of the ideal result, then work backwards.
3. Layer CTEs: filters → joins → aggregates → windows.
4. Check NULL / anti-join behavior on LEFT JOIN.
5. Prefer half-open date ranges; know your dialect’s date helpers.
6. If asked about speed: fewer columns, filter early, index join keys, avoid exploding joins.

Deeper practice sets: SQL, SQL Problems (easy), SQL Problems (medium). Next warm-up: Python interview cheat sheet.