Practice Examples
Curated SQL exercises from basic SELECTs to window functions and CTEs, with hints and solutions.
The best way to learn PostgreSQL is by writing SQL. This section provides curated exercises from basic SELECTs to intermediate JOINs and window functions. Each exercise includes a hint, the solution, and expected output.
Real-World Stories
Stripe - The Missing Index That Cost $1M in Compute
A Stripe engineer deployed a new dashboard feature that ran SELECT * FROM payments WHERE merchant_id = ? ORDER BY created_at DESC on a 2-billion-row table without a composite index. The query triggered sequential scans, pegging CPU at 100% across 8 read replicas. The fix was a single CREATE INDEX CONCURRENTLY on (merchant_id, created_at DESC). Query time dropped from 12 seconds to 3ms.
Datadog - Teaching SQL Through Production Incidents
Datadog created an internal "SQL Gym" where engineers practice writing queries against anonymized production schemas. New hires solve exercises based on real incident investigations: "Find the top 10 customers by ingested bytes in the last hour" or "Identify metrics with the highest cardinality growth." Engineers who completed the program resolved SQL-related incidents 60% faster than those who didn't.
Sample Schema
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
salary NUMERIC(10,2),
hire_date DATE
);
INSERT INTO employees VALUES
(1, 'Alice', 'Engineering', 95000, '2020-01-15'),
(2, 'Bob', 'Marketing', 72000, '2019-06-01'),
(3, 'Carol', 'Engineering', 105000, '2018-03-20'),
(4, 'Dave', 'Sales', 68000, '2021-09-10'),
(5, 'Eve', 'Engineering', 115000, '2017-11-30');Basic Exercises
1. SELECT with ORDER BY
Challenge: Get all employees sorted by hire_date (oldest first).
Show hint
Use ORDER BY with the hire_date column. Default sort order is ascending.
Show solution
SELECT * FROM employees ORDER BY hire_date;2. WHERE Filtering
Challenge: Find all Engineering employees earning more than $100,000.
Show hint
Use WHERE with AND to combine two conditions.
Show solution
SELECT * FROM employees
WHERE department = 'Engineering' AND salary > 100000;3. GROUP BY & Aggregation
Challenge: Get the average salary and employee count for each department.
Show hint
Use GROUP BY department with AVG() and COUNT().
Show solution
SELECT department, COUNT(*) AS emp_count,
ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;4. DISTINCT Values
Challenge: List all unique departments, sorted alphabetically.
Show hint
Use SELECT DISTINCT to eliminate duplicates from a column.
Show solution
SELECT DISTINCT department FROM employees ORDER BY department;5. HAVING Clause
Challenge: Find departments that have more than 1 employee.
Show hint
HAVING is like WHERE, but for filtering groups after GROUP BY.
Show solution
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;6. String Functions
Challenge: Show each employee name in uppercase, department in lowercase, and the length of their name.
Show hint
Use UPPER(), LOWER(), and LENGTH() functions.
Show solution
SELECT UPPER(name) AS upper_name,
LOWER(department) AS lower_dept,
LENGTH(name) AS name_length
FROM employees ORDER BY name;Intermediate Exercises
7. JOINs
Challenge: List each employee with their department location and budget using LEFT JOIN.
Show solution
SELECT e.name, e.salary, d.location, d.budget
FROM employees e
LEFT JOIN departments d ON e.department = d.name
ORDER BY e.name;8. CASE Expressions
Challenge: Classify employees as "Senior" (salary >= 100k), "Mid" (>= 70k), or "Junior".
Show hint
Use CASE WHEN ... THEN ... ELSE ... END to create conditional categories.
Show solution
SELECT name, salary,
CASE
WHEN salary >= 100000 THEN 'Senior'
WHEN salary >= 70000 THEN 'Mid'
ELSE 'Junior'
END AS level
FROM employees
ORDER BY salary DESC;9. Window Functions
Challenge: Rank employees within their department by salary (highest = rank 1).
Show hint
Use RANK() OVER (PARTITION BY department ORDER BY salary DESC).
Show solution
SELECT name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees
ORDER BY department, dept_rank;10. Self JOIN
Challenge: Find all pairs of employees in the same department (avoid duplicates like Alice-Carol and Carol-Alice).
Show hint
JOIN the table with itself on department, and use e1.id < e2.id to avoid duplicate pairs.
Show solution
SELECT e1.name AS employee_1, e2.name AS employee_2, e1.department
FROM employees e1
JOIN employees e2 ON e1.department = e2.department AND e1.id < e2.id
ORDER BY e1.department, e1.name;11. Date Functions
Challenge: Show each employee's tenure in years (as of 2024-01-01), rounded to 1 decimal.
Show hint
Use AGE() to get the interval, then EXTRACT(EPOCH FROM ...) / 86400 / 365.25 to convert to years.
Show solution
SELECT name, hire_date,
ROUND(EXTRACT(EPOCH FROM AGE('2024-01-01'::date, hire_date)) / 86400 / 365.25, 1) AS years_employed
FROM employees
ORDER BY years_employed DESC;12. CTEs (Common Table Expressions)
Challenge: Find active projects where the lead earns above the company average.
Show hint
Use a CTE to calculate the average salary first, then CROSS JOIN it into the main query.
Show solution
WITH avg_sal AS (
SELECT AVG(salary) AS company_avg FROM employees
)
SELECT p.name AS project, e.name AS lead, e.salary
FROM projects p
JOIN employees e ON p.lead_id = e.id
CROSS JOIN avg_sal
WHERE p.status = 'active'
AND e.salary > avg_sal.company_avg
ORDER BY e.salary DESC;