Quick Reference Print-friendly • Last-minute revision

SQL Cheatsheet 🗃️

Essential SQL syntax, JOINs, window functions, and common query patterns for quick revision before interviews.

🔗 JOINs

-- INNER JOIN: Only matching rows
SELECT * FROM A INNER JOIN B ON A.id = B.a_id;

-- LEFT JOIN: All from A + matching from B (NULL if no match)
SELECT * FROM A LEFT JOIN B ON A.id = B.a_id;

-- FULL OUTER JOIN: All rows from both
SELECT * FROM A FULL OUTER JOIN B ON A.id = B.a_id;

-- CROSS JOIN: Cartesian product (every combo)
SELECT * FROM A CROSS JOIN B;

-- SELF JOIN: Table joins itself
SELECT e.name, m.name as manager
FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

🪟 Window Functions

-- ROW_NUMBER: Unique sequential (1,2,3,4)
-- RANK: Ties get same, gaps after (1,1,3,4)
-- DENSE_RANK: Ties same, no gaps (1,1,2,3)

SELECT name, dept, salary,
  ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) as rn,
  RANK() OVER (ORDER BY salary DESC) as rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) as drnk,
  LAG(salary) OVER (ORDER BY hire_date) as prev_salary,
  LEAD(salary) OVER (ORDER BY hire_date) as next_salary,
  SUM(salary) OVER (PARTITION BY dept) as dept_total,
  AVG(salary) OVER (ORDER BY hire_date ROWS 3 PRECEDING) as moving_avg
FROM employees;

📝 CTEs & Subqueries

-- CTE (Common Table Expression)
WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 100000
)
SELECT dept, COUNT(*) FROM high_earners GROUP BY dept;

-- Recursive CTE (hierarchies)
WITH RECURSIVE hierarchy AS (
  SELECT id, name, 1 as level FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, h.level + 1
  FROM employees e JOIN hierarchy h ON e.manager_id = h.id
)
SELECT * FROM hierarchy;

-- Correlated Subquery
SELECT * FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept);

🎯 Common Interview Patterns

-- 2nd highest salary
SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp);

-- Duplicate rows
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

-- Running total
SELECT date, amount, SUM(amount) OVER (ORDER BY date) as running_total FROM orders;

-- Top N per group
SELECT * FROM (
  SELECT *, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as rnk
  FROM employees
) t WHERE rnk <= 3;

-- Consecutive days (gaps & islands)
SELECT id, date, date - ROW_NUMBER() OVER (ORDER BY date) * INTERVAL '1 day' as grp
FROM logins;

-- PIVOT (rows to columns)
SELECT * FROM scores
PIVOT (AVG(score) FOR subject IN ('Math', 'Science', 'English'));

Previous: ML Formulas Next: Flashcards