Find Third Transaction - Problem

You are given a table Transactions with the following columns:

  • user_id (int): The user identifier
  • spend (decimal): The amount spent in the transaction
  • transaction_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_date is a valid datetime
  • Each user has at least 1 transaction

Visualization

Tap to expand
Find Third Transaction INPUT user_id spend date 1 100 Jan-01 1 150 Jan-02 1 200 Jan-03 2 50 Jan-01 2 80 Jan-02 2 120 Jan-03 3 300 Jan-01 3 200 Jan-02 3 150 Jan-03 Legend: Valid 3rd txn (higher) Invalid (not higher) Columns: user_id, spend, transaction_date ALGORITHM STEPS 1 ROW_NUMBER() Assign row number per user ordered by date 2 LAG() Window Get spend of previous 2 transactions 3 Filter row_num = 3 Keep only 3rd transaction for each user 4 Compare Spending spend > lag1 AND spend > lag2 ROW_NUMBER() OVER( PARTITION BY user_id ORDER BY txn_date) LAG(spend,1), LAG(spend,2) OVER same partition FINAL RESULT Processing User 1: txn1: 100, txn2: 150, txn3: 200 200 > 150? YES 200 > 100? YES OK - Include in result Processing User 2: txn1: 50, txn2: 80, txn3: 120 120 > 80? YES 120 > 50? YES OK - Include in result Processing User 3: txn1: 300, txn2: 200, txn3: 150 150 > 200? NO EXCLUDED - spend decreased OUTPUT (ordered by user_id) user_id=1, spend=200, Jan-03 user_id=2, spend=120, Jan-03 Key Insight: Use ROW_NUMBER() with PARTITION BY user_id to identify the 3rd transaction, and LAG() window function to access previous transaction amounts. This allows single-pass comparison without self-joins, achieving O(n log n) complexity. The WHERE clause filters for row_num=3 AND spend greater than both preceding transactions. TutorialsPoint - Find Third Transaction | Optimal Solution

Related Problems

Asked in
Amazon 28 Microsoft 22 Google 19
23.4K Views
Medium Frequency
~18 min Avg. Time
847 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen