User's 3rd Transaction
ASKED IN INTERVIEWUber
Problem Statement
Uber's growth team wants to analyze the third transaction of every user. The third transaction is often a "make or break" point for user retention.
Write an SQL query to find the details of the third transaction for every user.
Rules:
- A user must have at least three transactions to be included in the output.
- If a user has more than three transactions, only return the third one.
- The third transaction is defined by the chronological order of the transaction_date.
- Return columns: user_id, spend, and transaction_date.
- Results must be sorted by user_id in ascending order.
Tests your understanding of
Window Functions, Ranking, Subqueries and Analytics
Input Tables
transactions
| user_id(INTEGER) | spend(DECIMAL) | transaction_date(TIMESTAMP) |
|---|---|---|
| 1 | 15.5 | 2022-01-01 12:00:00 |
| 1 | 20 | 2022-01-02 10:00:00 |
| 1 | 12 | 2022-01-03 15:00:00 |
| 1 | 35 | 2022-01-04 11:00:00 |
| 2 | 10 | 2022-01-01 08:00:00 |
| 2 | 15 | 2022-01-02 09:00:00 |
| 3 | 50 | 2022-01-01 13:00:00 |
| 3 | 15 | 2022-01-05 14:00:00 |
| 3 | 25 | 2022-01-10 10:00:00 |
| 4 | 5 | 2022-02-01 12:00:00 |
| 4 | 10 | 2022-02-05 15:00:00 |
| 4 | 15 | 2022-02-10 09:00:00 |
| 4 | 20 | 2022-02-15 14:00:00 |
| 5 | 100 | 2022-03-01 10:00:00 |
| 5 | 200 | 2022-03-02 11:00:00 |
Expected Output
| user_id(INTEGER) | spend(DECIMAL) | transaction_date(TIMESTAMP) |
|---|---|---|
| 1 | 12 | 2022-01-03 15:00:00 |
| 3 | 25 | 2022-01-10 10:00:00 |
| 4 | 15 | 2022-02-10 09:00:00 |
Tags
MediumASKED IN INTERVIEWWindow FunctionsRankingSubqueriesAnalytics
15-20 min
55%