Find Third Transaction - Practice Coding Problems

Find Third Transaction - Problem

Database Medium

You are analyzing user spending patterns on an e-commerce platform to identify users who show increasing spending confidence. Your task is to find the third transaction for each user where they demonstrate escalating purchase behavior.

Given a Transactions table with user spending data, you need to identify users whose third transaction amount is higher than both their first and second transactions.

Key Requirements:

Only consider users with at least 3 transactions
The third transaction must have higher spending than both preceding transactions
Return results ordered by user_id in ascending order

This problem helps identify users who are becoming more engaged and willing to spend more over time - valuable insights for marketing and customer retention strategies!

Table Schema:

Transactions
+------------------+----------+
| Column Name      | Type     |
+------------------+----------+
| user_id          | int      |
| spend            | decimal  |
| transaction_date | datetime |
+------------------+----------+

Input & Output

example_1.sql — Basic Case

$ Input: Transactions table: | user_id | spend | transaction_date | |---------|-------|--------------------| | 1 | 65.56 | 2023-11-18 13:49:42| | 1 | 96.0 | 2023-11-30 02:47:04| | 1 | 7.44 | 2023-11-02 12:15:23| | 1 | 49.78 | 2023-11-12 00:13:46| | 2 | 40.89 | 2023-11-21 08:12:32| | 2 | 100.44| 2023-12-0901:16:05 | | 2 | 37.33 | 2023-11-11 16:22:04| | 3 | 37.0 | 2023-12-07 09:10:25|

› Output: | user_id | spend | transaction_date | |---------|-------|--------------------| | 1 | 65.56 | 2023-11-18 13:49:42|

💡 Note: For user 1, transactions in chronological order: $7.44 (1st), $49.78 (2nd), $65.56 (3rd). The third transaction ($65.56) is greater than both previous ones. User 2's third transaction ($100.44) is not greater than the second ($37.33 < $100.44 is false when comparing 1st < 3rd). User 3 has only one transaction.

example_2.sql — Multiple Qualifying Users

$ Input: Transactions table: | user_id | spend | transaction_date | |---------|-------|--------------------| | 1 | 10.0 | 2023-01-01 10:00:00| | 1 | 15.0 | 2023-01-02 10:00:00| | 1 | 25.0 | 2023-01-03 10:00:00| | 2 | 5.0 | 2023-01-01 11:00:00| | 2 | 12.0 | 2023-01-02 11:00:00| | 2 | 18.0 | 2023-01-03 11:00:00| | 3 | 20.0 | 2023-01-01 12:00:00| | 3 | 30.0 | 2023-01-02 12:00:00| | 3 | 25.0 | 2023-01-03 12:00:00|

› Output: | user_id | spend | transaction_date | |---------|-------|--------------------| | 1 | 25.0 | 2023-01-03 10:00:00| | 2 | 18.0 | 2023-01-03 11:00:00|

💡 Note: User 1: $10 < $15 < $25 ✓. User 2: $5 < $12 < $18 ✓. User 3: $20 < $30 but $30 > $25, so third transaction is not greater than second ✗.

example_3.sql — Edge Cases

$ Input: Transactions table: | user_id | spend | transaction_date | |---------|-------|--------------------| | 1 | 100.0 | 2023-01-01 10:00:00| | 1 | 50.0 | 2023-01-02 10:00:00| | 1 | 75.0 | 2023-01-03 10:00:00| | 2 | 10.0 | 2023-01-01 11:00:00| | 2 | 10.0 | 2023-01-02 11:00:00| | 3 | 15.0 | 2023-01-01 12:00:00|

› Output: | user_id | spend | transaction_date | |---------|-------|-----------------| | (empty result set) |

💡 Note: User 1: $75 is not greater than $100 (first transaction). User 2: Has only 2 transactions. User 3: Has only 1 transaction. No users meet the criteria.

Constraints

1 ≤ Number of transactions ≤ 10⁵
1 ≤ user_id ≤ 10⁴
0.01 ≤ spend ≤ 1000.00
Each (user_id, transaction_date) combination is unique
transaction_date is in format 'YYYY-MM-DD HH:MM:SS'

Visualization

Tap to expand

Understanding the Visualization

Organize by Customer

Group all transactions by user_id to analyze each customer separately

Sort by Time

Order transactions chronologically to identify the sequence of purchases

Number the Purchases

Assign sequence numbers (1st, 2nd, 3rd, etc.) to each transaction

Compare Spending

Check if the 3rd transaction amount exceeds both the 1st and 2nd

Select Winners

Return customers whose 3rd purchase shows increasing confidence

Key Takeaway

🎯 Key Insight: Window functions allow us to efficiently partition data by user and access previous row values for comparison, making this a perfect use case for LAG() and ROW_NUMBER() functions in a single query pass.

Asked in

a Amazon 45 G Google 38 f Meta 32 ⊞ Microsoft 28

The optimal solution uses window functions with ROW_NUMBER() and LAG() to efficiently identify and compare the first three transactions for each user in a single query pass. This approach leverages modern SQL capabilities to achieve O(n log n) time complexity, significantly outperforming the brute-force subquery approach that requires multiple table scans.

Common Approaches

Approach	Time	Space	Notes
✓ Window Functions (Optimal)	O(n log n)	O(n)	Use window functions to rank transactions and compare spending in a single efficient query
Subquery Approach (Brute Force)	O(n²)	O(n)	Use nested subqueries to find and compare the first three transactions for each user

Window Functions (Optimal) — Algorithm Steps

Use ROW_NUMBER() window function to rank transactions by date for each user
Use LAG() window function to access previous transaction amounts
Filter for third transactions where spend > both previous transactions
Order results by user_id

Visualization

Tap to expand

Step-by-Step Walkthrough

Partition by User

Group transactions by user_id for window function processing

Order by Date

Sort transactions chronologically within each user partition

Apply ROW_NUMBER

Assign sequence numbers to identify 1st, 2nd, 3rd transactions

Use LAG Functions

Access previous transaction amounts for comparison

Filter & Compare

Select 3rd transactions where spend > previous two

Code -

solution.c — C

// Optimal SQL solution using window functions
char* query = "\
WITH ranked_transactions AS ( \
    SELECT \
        user_id, \
        spend, \
        transaction_date, \
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY transaction_date) as rn, \
        LAG(spend, 1) OVER (PARTITION BY user_id ORDER BY transaction_date) as prev_spend_1, \
        LAG(spend, 2) OVER (PARTITION BY user_id ORDER BY transaction_date) as prev_spend_2 \
    FROM Transactions \
) \
SELECT user_id, spend, transaction_date \
FROM ranked_transactions \
WHERE rn = 3 \
  AND spend > prev_spend_1 \
  AND spend > prev_spend_2 \
ORDER BY user_id;";

typedef struct {
    int user_id;
    double spend;
    char transaction_date[20];
} TransactionResult;

int findThirdTransaction(sqlite3* db, TransactionResult** results) {
    sqlite3_stmt* stmt;
    int rc = sqlite3_prepare_v2(db, query, -1, &stmt, NULL);
    if (rc != SQLITE_OK) return -1;
    
    int count = 0;
    *results = malloc(1000 * sizeof(TransactionResult));
    
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        (*results)[count].user_id = sqlite3_column_int(stmt, 0);
        (*results)[count].spend = sqlite3_column_double(stmt, 1);
        strcpy((*results)[count].transaction_date, 
               (char*)sqlite3_column_text(stmt, 2));
        count++;
    }
    
    sqlite3_finalize(stmt);
    return count;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single scan with sorting for window function partitioning

⚡ Linearithmic

Space Complexity

O(n)

Space for window function computation and result set

⚡ Linearithmic Space

42.3K Views

Medium-High Frequency

~25 min Avg. Time

1.8K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Window Functions (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler