↓Skip to main content

The SQL Checklist for Data Science

Use this interactive page to practice all the SQL you need for data science.

Each concept is a flash card. Say or write out each concept, then click on the item to reveal the answer. Check the box when you know it and feel confident. See Part 1 and Part 2 for more information on each item.

0 / 0 checked
01

Selecting & Filtering

Selecting, filtering, and ordering rows are the most basic operations. See Part 1 for full explanations.

Basic select, limit +
select col from t limit 10;
Ordering — asc/desc, multi-column +
order by a asc, b desc
Filtering with where +
where x = 'y' and z > 100
In / not in +
where x in (1, 2, 3)
Is null +
where x is null
02

Joins

Joins combine rows from two tables based on matching columns. It’s one of the most powerful features of the language. See Part 1 for the full explanation, including anti, self, cross, and range joins.

Basic join, table aliasing +
from a left join b on b.id = a.id
Join types & unmatched rows +
inner: match only left: all left, null right if no match right: all right, null left if no match full outer: matched rows + unmatched from both sides (nulls fill gaps)
Anti join +
left join ... where b.id is null
Self join +
from t a join t b on a.parent_id = b.id
Cross join +
from a cross join b
Range join +
on a.date >= b.valid_from and a.date < b.valid_to
03

Grouping & Aggregating

Aggregating functions allow you to summarize many rows. Grouping allows you to perform the same operation for many subunits. See Part 1 for the full explanations.

Group by, count/sum/avg/min/max +
group by col
Distinct inside aggregates +
count(distinct col)
Median +
percentile_cont(0.5) within group (order by col)
Handling nulls in aggregates +
coalesce(col, 0)
04

Organizing Complex Queries

CTEs and intermediate tables are a must for keeping complex, multi-step queries readable and re-runnable. See Part 2 for the full explanations.

CTEs +
with x as (select ...) select * from x
Intermediate tables +
create table x as select ...
05

Window Functions

Window functions let you calculate a value across multiple rows without collapsing them, like a running total or the top row per group. See Part 2 for the full explanations.

Over, partition by, order by +
sum(x) over (partition by id order by date)
Top row per group +
row_number() over (partition by id order by date desc)
06

Dates, Strings & New Columns

Working with dates, strings, and conditional logic to shape and categorize your data. See Part 2 for the full explanations.

Date truncation +
date_trunc('month', d)
Date arithmetic +
d + interval '30 days'
String concatenation +
concat(a, ' ', b)
Pattern matching +
where x like '%@gmail.com'
Case when +
case when x >= 1000 then 'large' else 'small' end
07

Sampling, Reshaping & Utilities

Sampling large data for fast iteration, reshaping arrays, and finding your way around unfamiliar tables. See Part 2 for the full explanations.

Sampling rows +
tablesample bernoulli (1) repeatable (1234)
Hash sampling (reproducible outside the database) +
mod(get_byte(decode(md5(id::text), 'hex'), 0), 100) < 5
Exploding / imploding arrays +
unnest(arr) · array_agg(x)
Finding tables/columns via system tables +
select table_name, column_name from information_schema.columns where column_name like '%id%'
Filling gaps with generate_series +
generate_series('2026-01-01'::date, '2026-01-31'::date, interval '1 day')