Ola mid ROW_NUMBERPARTITION BYOVERORDER BYCTE

The trips table sometimes has multiple rows for the same trip_id, logged because of app retries. For each trip_id, keep only the row with the most recent updated_at and discard the older duplicate rows. Return trip_id, driver_id, rider_id, fare, and updated_at for the deduplicated trips, sorted by trip_id.

Ola · Trip Data Quality — practice this real-world SQL scenario live in your browser.

📖 Story Ola · Trip Data Quality

Tuesday afternoon at Ola's Data Platform team. The mobile app has a habit of silently retrying a trip update if the network blips mid-request, and each retry writes a brand new row into the trips table instead of updating the existing one. Some trips now have two or three rows logged for the same trip_id, each with a slightly different updated_at timestamp — and sometimes a different fare, if the retry happened after the fare was recalculated. Before this table can be trusted for reporting, every trip needs to collapse down to exactly one row: its most recently updated version.

🎯 Your Mission

📋 Table Structure

🗂 trips

trip_id TEXT TR1001

driver_id INTEGER 501

rider_id INTEGER 301

fare REAL 275.00

updated_at TEXT 2026-01-10 09:10:00

⚡ Step-by-Step Walkthrough

Look at the raw trips table and spot the duplicates

query.sql

SELECT *
FROM trips
ORDER BY trip_id, updated_at;

💡 Explanation

The trips table has one row per logged update, not one row per trip — trip_id repeats whenever the app retried, so a single real trip can show up two or three times.
Sorting by trip_id and updated_at makes the duplicates easy to see side by side: TR1001 has three rows, TR1004 has two, and TR1002 and TR1005 have only one each — not every trip was retried.
TR1003 is the trickiest case: two rows with the exact same updated_at timestamp, down to the second. A retry that happens to log at the identical second as the original produces a genuine tie, not just a near-duplicate.
Nothing is being removed yet — this step is purely about confirming what the raw data actually looks like before deciding how to collapse it.

Assign a rank to each row within its trip_id group, most recent first

query.sql

SELECT trip_id, driver_id, rider_id, fare, updated_at,
       ROW_NUMBER() OVER (PARTITION BY trip_id ORDER BY updated_at DESC) AS rn
FROM trips
ORDER BY trip_id, rn;

💡 Explanation

PARTITION BY trip_id groups the rows by trip, the same way GROUP BY would — except every row stays visible in the output instead of collapsing into one summary row per group.
ORDER BY updated_at DESC, inside the OVER() clause, decides the order ROW_NUMBER() counts within each partition — the most recently updated row in each trip_id group gets counted first, so it receives rn = 1.
ROW_NUMBER() assigns 1, 2, 3... within each partition, restarting at 1 every time trip_id changes. TR1001's three rows get rn 1, 2, 3; TR1002's single row gets rn 1 by itself, since a partition with only one row still starts counting from 1.
For TR1003's tied pair, both rows share the identical updated_at, so which one becomes rn = 1 and which becomes rn = 2 is arbitrary — SQLite has to pick an order somehow, but nothing in the data says which row is truly 'more recent.' Here both rows are otherwise identical anyway, so it doesn't change the final answer — but a genuine tie in the ORDER BY column is resolved arbitrarily, not meaningfully.

Wrap it in a CTE and keep only the latest row per trip

query.sql

WITH ranked_trips AS (
  SELECT trip_id, driver_id, rider_id, fare, updated_at,
         ROW_NUMBER() OVER (PARTITION BY trip_id ORDER BY updated_at DESC) AS rn
  FROM trips
)
SELECT trip_id, driver_id, rider_id, fare, updated_at
FROM ranked_trips
WHERE rn = 1
ORDER BY trip_id;

💡 Explanation

The ranking query from the previous step becomes a CTE (ranked_trips) so its output can be filtered — rn is a computed column that only exists after the window function runs, and a WHERE clause can't filter on it in the same SELECT where it's defined.
WHERE rn = 1 keeps exactly one row per trip_id: the one ROW_NUMBER() counted first, which is the one with the latest updated_at because of how the OVER() clause was ordered.
Nine raw rows collapse to five — one per distinct trip_id. TR1001 keeps its 09:10:00 record at fare 275, not the earlier 250 from 09:00:00; TR1004 keeps its 11:20:00 record at fare 165, not the original 150.
ORDER BY trip_id on the outer query is a separate, ordinary sort of the final deduplicated result — it has nothing to do with the ORDER BY inside OVER(), which only controls how rn was assigned.

▶ Practice this live ← Browse all questions