latest row per customer without a correlated subquery that takes 40 minutes?
Aoife Brennan@aoifeb_dev
doing a take-home for a data engineering role (job hunt day 187, grand, all grand). table is basically:
customer_events (
id bigint primary key,
customer_id bigint,
event_type text,
payload jsonb,
created_at timestamptz
)about 120M rows in their description, i've a 5M sample locally. they want the latest event per customer. my first go:
SELECT e.*
FROM customer_events e
WHERE e.created_at = (
SELECT MAX(created_at)
FROM customer_events e2
WHERE e2.customer_id = e.customer_id
);on the 5M sample i killed it after 25 minutes. in spark i'd just do a window function and go home, but the brief says plain SQL, postgres 15, and "portable where possible". also two events can have the same created_at and they want exactly one row per customer. what's the proper way to do this, and is there an index that makes it not awful? sure look, any help appreciated
Accepted answer
ok this one I do every week lah. three ways:
1. postgres only, shortest:
SELECT DISTINCT ON (customer_id) id, customer_id, event_type, payload, created_at
FROM customer_events
ORDER BY customer_id, created_at DESC, id DESC;
DISTINCT ON keeps the first row per customer_id according to the ORDER BY. id DESC breaks the tie when created_at is same.
2. portable, works in SQL Server, Oracle, MySQL 8, Snowflake etc:
SELECT id, customer_id, event_type, payload, created_at
FROM (
SELECT e.*,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC, id DESC) AS rn
FROM customer_events e
) t
WHERE rn = 1;
ROW_NUMBER not RANK, because RANK gives 2 rows when tie. confirm they will ask this in the follow-up.
3. when there are few customers and very many events each, LATERAL with LIMIT 1 can be fastest, it jumps into the index once per customer:
SELECT c.id, last.*
FROM customers c
CROSS JOIN LATERAL (
SELECT e.id, e.event_type, e.payload, e.created_at
FROM customer_events e
WHERE e.customer_id = c.id
ORDER BY e.created_at DESC, e.id DESC
LIMIT 1
) last;
for all three the index is:
CREATE INDEX ON customer_events (customer_id, created_at DESC, id DESC);
and since we are in select-star-anonymous: list the columns in your submission, the reviewer will notice 😄
Get the best of #select-star-anonymous weekly
One email each Monday with the most useful posts. No spam; unsubscribe in one click.
Comments
Sign in to join the conversation.
Sign in to commentLin Wei@linwei
DISTINCT ON is the postgres way. keep id as tiebreaker or the result is not deterministic
Accepted answer
Nurul Aisyah@nurul_sql
ok this one I do every week lah. three ways:
1. postgres only, shortest:
SELECT DISTINCT ON (customer_id) id, customer_id, event_type, payload, created_at
FROM customer_events
ORDER BY customer_id, created_at DESC, id DESC;
DISTINCT ON keeps the first row per customer_id according to the ORDER BY. id DESC breaks the tie when created_at is same.
2. portable, works in SQL Server, Oracle, MySQL 8, Snowflake etc:
SELECT id, customer_id, event_type, payload, created_at
FROM (
SELECT e.*,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC, id DESC) AS rn
FROM customer_events e
) t
WHERE rn = 1;
ROW_NUMBER not RANK, because RANK gives 2 rows when tie. confirm they will ask this in the follow-up.
3. when there are few customers and very many events each, LATERAL with LIMIT 1 can be fastest, it jumps into the index once per customer:
SELECT c.id, last.*
FROM customers c
CROSS JOIN LATERAL (
SELECT e.id, e.event_type, e.payload, e.created_at
FROM customer_events e
WHERE e.customer_id = c.id
ORDER BY e.created_at DESC, e.id DESC
LIMIT 1
) last;
for all three the index is:
CREATE INDEX ON customer_events (customer_id, created_at DESC, id DESC);
and since we are in select-star-anonymous: list the columns in your submission, the reviewer will notice 😄
Aoife Brennan@aoifeb_dev
nurul you absolute legend. DISTINCT ON with the index ran in 9 seconds on the 5M sample. going with ROW_NUMBER in the submission since they said portable, plus a note about DISTINCT ON and the LATERAL one to look smart
Nurul Aisyah@nurul_sql
good choice lah, the note will impress them. explain the id DESC tie break in the call and you are confirm fine
Sai Teja Reddy@teja_dataeng
the lateral one is underrated anna, use it when customers table is small and events are huge
Imran Hussain@imran_builds
not my area but sent you another lead aoife, data eng contract in Leeds, remote mostly. they're on Postgres so you're sorted after this
Aoife Brennan@aoifeb_dev
imran you're a gem. at this rate i'll owe you a pint in every city in england
Halima Bello@halima_data
Please this is very helpful. Thank you. Please does the ROW_NUMBER version work in SQL Server also? We use it at work.
Nurul Aisyah@nurul_sql
yes Halima, ROW_NUMBER works the same in SQL Server. DISTINCT ON is postgres only. in SQL Server you can also use CROSS APPLY with TOP 1, same idea as the lateral