Find Third Transaction - Problem
You are given a table Transactions with the following columns:
user_id(int): The user identifierspend(decimal): The amount spent in the transactiontransaction_date(datetime): The date and time of the transaction
The combination of (user_id, transaction_date) is unique for each row.
Write a SQL query to find the third transaction for each user who has at least three transactions, where:
- The spending on the first transaction is lower than the spending on the third transaction
- The spending on the second transaction is lower than the spending on the third transaction
Return the result ordered by user_id in ascending order.
Table Schema
Transactions
| Column Name | Type | Description |
|---|---|---|
user_id
PK
|
int | User identifier |
spend
|
decimal | Amount spent in the transaction |
transaction_date
PK
|
datetime | Date and time of the transaction |
Primary Key: (user_id, transaction_date)
Note: Each user can have multiple transactions, but the combination of user_id and transaction_date is unique
Input & Output
Example 1 — Basic Third Transaction
Input Table:
| user_id | spend | transaction_date |
|---|---|---|
| 1 | 100 | 2024-01-01 |
| 1 | 120 | 2024-01-02 |
| 1 | 200 | 2024-01-03 |
| 2 | 50 | 2024-01-01 |
| 2 | 80 | 2024-01-02 |
| 2 | 70 | 2024-01-03 |
Output:
| user_id | spend | transaction_date |
|---|---|---|
| 1 | 200 | 2024-01-03 |
💡 Note:
User 1's third transaction (200.00) is greater than both first (100.00) and second (120.00) transactions, so it's included. User 2's third transaction (70.00) is not greater than the second transaction (80.00), so it's excluded.
Example 2 — User with Less Than Three Transactions
Input Table:
| user_id | spend | transaction_date |
|---|---|---|
| 3 | 150 | 2024-01-01 |
| 3 | 200 | 2024-01-02 |
| 4 | 75 | 2024-01-01 |
| 4 | 85 | 2024-01-02 |
| 4 | 95 | 2024-01-03 |
Output:
| user_id | spend | transaction_date |
|---|---|---|
| 4 | 95 | 2024-01-03 |
💡 Note:
User 3 has only 2 transactions, so no result for them. User 4's third transaction (95.00) is greater than both first (75.00) and second (85.00) transactions, so it's included.
Constraints
-
1 ≤ user_id ≤ 1000 -
0.01 ≤ spend ≤ 10000.00 -
transaction_dateis a valid datetime - Each user has at least 1 transaction
Visualization
Tap to expand
Understanding the Visualization
1
Input
Transactions table with user spending history
2
Window Functions
ROW_NUMBER and LAG to rank and compare
3
Filter
Third transactions exceeding first two amounts
Key Takeaway
🎯 Key Insight: Use window functions to rank transactions chronologically and compare current amounts with previous transactions efficiently
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code