All the SQL You Need For Data Science, Part 2
Table of Contents
This part continues Part 1, which covered selecting, filtering, joining, and aggregating. This part covers organizing complex queries, window functions, dates and strings, sampling, and more. As before, we are using PostgreSQL syntax.
Breaking up and organizing #
What starts out as a simple few lines of SQL tends to quickly grow complicated. There are edge cases to take into account. Maybe additional filtering based on criteria that you have to calculate from another table. Additional data that you want to join in.
While you can nest queries, so that you filter on a value computed by a
separate select, this quickly becomes hard to read. In the example below, notice how there is a whole select ... statement inside parentheses.
select
*
from
orders
where
amount > ( -- subquery inside parentheses replaces one value in the top query.
select
max(amount)
from
orders
where
status = 'refunded' -- filter using a value computed in a subquery
)
;
A better way to organize complex queries is with a CTE (Common Table
Expression), which lets you name each step and read the entire query top to
bottom. CTEs use the syntax with <<CTE_NAME>> as (...) and are separated by commas. The query must still end with a naked select statement.
with
completed_orders as (
select
*
from
orders
where
status = 'completed'
)
, customer_totals as (
select
customer_id
, sum(amount) as total_amount -- reference the CTE above by name
from
completed_orders
group by
customer_id
)
select -- final select statement
*
from
customer_totals -- refers to CTE name defined above.
order by
total_amount desc
;
CTEs are an excellent tool for organizing any query that requires more than one subquery.
Intermediate tables #
CTEs allow you to break complex operations into more manageable chunks, and so are great for keeping the logic straight when writing and reading queries. But, their results don’t persist, they have to be executed every time you run the query.
Sometimes you have queries that for whatever reason (large data, few resources, complex logic) take a long time to run. When you are building up (or tearing down, trying to understand) complex analyses, or, when the runtime is long and the server drops your connection, you can use intermediate tables in the same way as CTEs, but with the crucial benefit that results persist.
Don’t use temporary tables for this purpose, they don’t persist across sessions.
But make sure you use a schema (namespace) and naming convention that keeps
these intermediate tables clearly marked as such. In the example below we use scratch as an example schema.
drop table if exists
scratch.da_customer_totals -- lets you safely rerun the script from scratch
;
create table
scratch.da_customer_totals
as
select
customer_id
, sum(amount) as total_amount -- prefix with your initials to avoid clashes
from
orders
where
status = 'completed'
group by
customer_id
;
The session can be dropped, or the query can run overnight, or you can come back to this step after the weekend, and, the table will still be there.
select
*
from
scratch.da_customer_totals
order by
total_amount desc
limit
10
;
Advanced aggregation (window functions) #
When you group by columns and do an aggregation, rows are collapsed, so that you end up with fewer rows than before (one per value of the grouping columns). Sometimes, you want to calculate a value that references multiple rows, but, without collapsing those rows. Examples include calculating a row number, or a running total, or moving average.
Window functions let you do this. A window function takes a function that operates over multiple rows, and specifies the window over which it is applied, hence the over syntax. The window
specification allows both partitioning (grouping), and ordering.
select
customer_id
, order_id
, order_date
, amount
, sum(amount) over (partition by customer_id order by order_date) as running_total -- runs per customer, ordered by date
, row_number() over (partition by customer_id order by order_date desc) as rn
from
orders
;
A common use case is to only pick the top (or some other number) row within a grouping.
select
*
from
(
select
customer_id
, order_id
, amount
, row_number() over (partition by customer_id order by amount desc) as rn
from
orders
) t
where
rn = 1 -- keep only the top row per group
;
Practice This
Get all the concepts in this guide as interactive flashcards and a checklist. We won't send you spam. Unsubscribe at any time.
Working with special column types (dates and strings) #
Dates and Times #
The main things that you might need to do with dates are first, aggregating a smaller time unit up to a larger one, like from a date and time to a day, or a day to a month. The second is to perform some date or time arithmetic, such as adding or subtracting time from a date, or, calculating the length of a time span.
Aggregating a smaller time unit to a larger unit can be done with date_trunc.
Notice how the unit you want to aggregate to is the first argument. Many other
units, such as hour, day, week, month, etc are available.
select
date_trunc('month', order_date) as order_month -- truncates to the first of the month
, count(*) as nr_orders
from
orders
group by
date_trunc('month', order_date)
;
To add or subtract time from a date (or timestamp), use the + interval syntax.
select
order_id
, order_date
, order_date + interval '30 days' as due_date -- add or subtract a fixed interval
from
orders
;
A time interval can be calculated just by subtracting two dates. Keep in mind
that if both are dates, then the result will be an integer, but, if one is a
timestamp, then the result will be an interval.
select
order_id
, order_date
, current_date - order_date as days_since_order -- calculate interval with `-`
from
orders
;
Strings #
The most basic thing you might do with two string columns is to concatenate them together, using the concat syntax.
select
concat(first_name, ' ', last_name) as full_name -- joins strings together
from
customers
;
To find or filter rows where strings match a pattern, you can use the like or
(case-insensitive ilike) operator. It matches two wildcards: %, which
matches any sequence, including empty, and, _, which matches exactly one
character.
select
*
from
customers
where
email like '%@gmail.com' -- % matches any characters
or first_name ilike 'j_n' -- ilike is case-insensitive; _ matches exactly one character
;
Creating new columns #
Sometimes you want to categorize rows by the values of a column, for example
into “large”, “medium”, “small” etc. In situations where you want to create a new column
based on conditions applied to another column, you can use the case expression.
The example shows the entire syntax. Don’t forget to end with an end. Cases
are evaluated top to bottom, so subsequent cases will only be evaluated if the
preceding ones don’t apply.
select
order_id
, amount
, case
when amount >= 1000 then 'large'
when amount >= 100 then 'medium'
else 'small'
end as order_size -- first matching condition wins
from
orders
;
Practice This
Get all the concepts in this guide as interactive flashcards and a checklist. We won't send you spam. Unsubscribe at any time.
Sampling #
Very often when you work with large amounts of data you want to sample, if nothing else for fast iteration when you are developing. Sometimes the data is so large that even final results can only be economically calculated on samples.
The fastest way to get a random sample of rows is to use tablesample. Use the keyword repeatable with a seed to make it reproducible, returning the same rows as long as the underlying table hasn’t changed.
select
*
from
orders
tablesample bernoulli (1) repeatable (1234) -- randomly samples ~1% of rows
;
While tablesample is very fast, it does not give you an exact number of rows.
If you do need an exact number, you can order by random(), and then use
limit. But this requires scanning the entire table and so is much slower. Just
using limit without the shuffling introduced by ordering by random does not
give you a random sample.
select
*
from
orders
order by
random()
limit
1000
;
If you are going to sample and then join two datasets together, sampling each independently and then joining is not going to give you good results, because a row has to be in both samples to then also end up in the join.
In cases like that you can use hash sampling, where you use a hash function to
randomly, but deterministically, map each row identifier to a new value. You can
then use modulus (mod) to divide the output of the hash function into equally
sized buckets that you can then use to select into your sample.
Hash sampling is also a way to make the sampling reproducible, and, if you
choose a commonly used hash function, like md5, even reproducible outside the database. So
you can perform the same hashing and modulus other places you encounter your
identifier and get the same random assignment.
In this example, md5 returns a hex string, which decode parses into binary,
from which get_byte pulls out the byte at position 0 as a number in the range
0 to 255. mod then divides that integer into 100 (almost) equally sized buckets.
select
*
from
orders
where
mod(get_byte(decode(md5(customer_id::text), 'hex'), 0), 100) < 5 -- md5 is portable outside the database, unlike hashtext
;
Exploding and imploding #
Sometimes, often when working with data in JSON format, you have to deal with
arrays. If you have an array, you might need to “explode” it into rows, so that
you get one row per array element (beware that this can end up being a lot of
rows). To do this you can use the slightly awkward cross join lateral with
unnest syntax.
select
customer_id
, tag
from
customer_tags
cross join lateral
unnest(tags) as tag -- one row per array element
;
Sometimes you want to gather up many rows into an array in one row, which you can do with array_agg.
select
customer_id
, array_agg(tag) as tags -- the inverse: collapse rows back into an array
from
customer_tags_exploded
group by
customer_id
;
Often you will use these in tandem, first exploding an array into rows, then doing an aggregation or calculation, and then imploding back into an array.
System tables #
Every database keeps metadata about its own tables and columns, queryable like any other table. This is useful for finding where a column lives, or seeing what’s currently running.
Sometimes (more likely, often) the data you are trying to access is poorly
documented. You can then search to find all the tables with a specific column
name, or relax the filter to just look for columns matching a string pattern (with like).
select
table_name
, column_name
, data_type
from
information_schema.columns
where
column_name = 'customer_id' -- find every table that has this column
;
Sometimes you are looking for a way to join two different tables together, without having a shared key. You can then search the database for tables where both keys are present as columns, which you could then potentially use as a mapping of the two keys.
select
table_name
from
information_schema.columns
where
column_name in ('customer_id', 'order_id')
group by
table_name
having
count(distinct column_name) = 2 -- tables containing both columns
;
I still recommend asking other people if you can, but, these results can be a starting point, showing that you made an effort to locate the data yourself.
Sometimes if you have a long-running query, you might want to check that it’s still running and how long it’s been running. You can do this by querying the activity table for queries.
select
pid
, state
, query
, now() - query_start as duration
from
pg_stat_activity
where
state = 'active'
order by
duration desc -- see what's currently running, and for how long
;
Practice This
Get all the concepts in this guide as interactive flashcards and a checklist. We won't send you spam. Unsubscribe at any time.
Creating spells or all elements #
One last thing you might need to do is create rows that don’t come from any table. A typical example is every date in a range, so you can see which days have no rows at all.
This can be done using the generate_series function. In this example we use dates but it also works for numbers and timestamps. Notice that we explicitly “cast” the results of the function to a date using the :: syntax.
select
generate_series(
'2026-01-01'::date
, '2026-01-31'::date
, interval '1 day'
)::date as date -- one row per day in the range
;
Once you have a table, or, CTE, with all the values in the desired range, you
can left join in all your observation rows, and, fill in null values (often
with zero) with coalesce. In this example we are also grouping and counting by
day.
with
all_dates as (
select
generate_series(
'2026-01-01'::date
, '2026-01-31'::date
, interval '1 day'
)::date as order_date
)
select
all_dates.order_date
, coalesce(count(orders.order_id), 0) as nr_orders -- fills in zero for days with no orders
from
all_dates
left join
orders
on orders.order_date = all_dates.order_date
group by
all_dates.order_date
order by
all_dates.order_date
;
What you don’t need #
That concludes everything that you need. While of course there are more things worth knowing, you will pick those up as you focus your work. But, there are a few areas I don’t think you need to focus on if you are a data scientist, i.e. mainly a consumer of data.
Variables. If you feel the need for variables, you are likely trying to do too much in SQL. My recommendation is to instead run the SQL from another language (e.g. Python, R, almost anything else) and insert values with SQL templating.
Views. Yes, as a data engineer or DBA these are useful, but, rarely used by data scientists.
Temp tables: better to create intermediate tables, temp tables are deleted when your session ends. Often when you are dealing with so much data or complexity that you need intermediate tables, you might get kicked out of your session.
Stored procedures: Again something that is useful for data engineers and DBAs, less so for data scientists.
One thing worth knowing about is indexes, which are how the database can make
joins and filters on specific columns fast. Essentially the database engine
takes the values of a column and stores them in a data structure that allows for
fast lookup. If a specific query is slow, checking if the involved columns have
an index, and, creating one if not, can help speed it up. Indexes are stored in
the pg_indexes system view, you can filter on the tablename for your table. Indexes
are created with the create index on syntax.