Each User's Third Purchase
Problem statement
The marketing team believes the third purchase is when a customer becomes a habit, and wants to study it. Write a query that returns every user's third purchase.
Table (SQLite dialect; timestamps are ISO-8601 text):
transactions(id INT, user_id INT, item TEXT, created_at TEXT, revenue REAL): one row per purchase,idunique,created_atas'YYYY-MM-DD HH:MM:SS'.
Task. Order each user's purchases by created_at, breaking ties by the smaller id. For every user with at least three purchases, return their third purchase. Users with fewer than three purchases do not appear.
Output. Columns, in order: user_id, id, item, created_at, revenue, ordered by user_id ascending.
Example input:
id | user_id | item | created_at | revenue
---+---------+--------+---------------------+--------
1 | 10 | shoes | 2026-03-01 09:00:00 | 80.0
2 | 10 | socks | 2026-03-02 10:15:00 | 12.5
3 | 20 | jacket | 2026-03-02 11:00:00 | 140.0
4 | 10 | hat | 2026-03-04 18:30:00 | 25.0
5 | 10 | bag | 2026-03-05 08:45:00 | 60.0
6 | 20 | scarf | 2026-03-06 12:00:00 | 30.0
Expected output:
user_id | id | item | created_at | revenue
--------+----+------+---------------------+--------
10 | 4 | hat | 2026-03-04 18:30:00 | 25.0
User 20 has only two purchases.
Show hint
Which family of SQL functions can label every row with its position inside a group without collapsing the rows?
Relevant roles: Data Scientist and Forward Deployed Engineer.