Objective

Write a SQL query to find the third transaction for each user from the transactions table.

Table Schema:

• transactions

Task Requirements:

• Identify the third transaction for each user based on transaction_date
• Return only users who have at least 3 transactions
• Display user_id, spend, and transaction_date for the third transaction
  
  

Output columns: user_id, spend, transaction_date

SELECT 
    user_id,
    spend,
    transaction_date
FROM (
    SELECT 
        user_id,
        spend,
        transaction_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY transaction_date ASC) AS transaction_rank
    FROM transactions
) AS ranked_transactions
WHERE transaction_rank = 3;

Explanation

Step 1: Understanding Window Functions

ROW_NUMBER() assigns a unique sequential number to each row within a partition, retaining all rows unlike GROUP BY.

Step 2: Partitioning by User

PARTITION BY user_id

Divides data into separate groups per user so ROW_NUMBER restarts at 1 for each user, ranking transactions independently.

Step 3: Ordering Transactions Chronologically

ORDER BY transaction_date ASC

Within each user's partition, assigns rank 1 to the earliest transaction, rank 2 to the second, rank 3 to the third, and so on.

Step 4: Filtering for the Third Transaction

WHERE transaction_rank = 3

Returns only the row with rank 3 per user (the third transaction). Users with fewer than 3 transactions are automatically excluded.