Objective

Write a SQL query to retrieve each user's most recent transaction date along with the number of products they bought on that date.

Table Schema:

• transactions

Task Requirements:

• For each user, identify their most recent transaction date
• Count the number of products purchased by that user on their most recent date
• A user may have bought multiple products on the same day, each as a separate transaction
• Sort the results chronologically by transaction date in ascending order
  
  

Output columns: user_id, transaction_date, num_products

WITH ranked AS (
    SELECT
        user_id,
        transaction_date,
        product_id,
        RANK() OVER (
            PARTITION BY user_id ORDER BY transaction_date DESC
        ) AS date_rank
    FROM transactions
)
SELECT
    user_id,
    transaction_date,
    COUNT(product_id) AS num_products
FROM ranked
WHERE date_rank = 1
GROUP BY user_id, transaction_date
ORDER BY transaction_date;

Explanation

Step 1: Ranking Transactions by Date

RANK() OVER (
    PARTITION BY user_id ORDER BY transaction_date DESC
) AS date_rank

RANK() assigns a ranking to each transaction within each user's group. PARTITION BY user_id restarts the ranking for every user, and ORDER BY transaction_date DESC puts the most recent date first. All transactions on the user's latest date receive date_rank = 1.

Step 2: Why RANK() Instead of ROW_NUMBER()

RANK() gives the same rank to ties. If a user bought 3 products on their most recent date, all 3 rows get rank 1. ROW_NUMBER() would assign ranks 1, 2, 3 to those same rows, causing 2 of the 3 to be lost when filtering. RANK() preserves all transactions from the latest date.

Step 3: Filtering for the Most Recent Date

WHERE date_rank = 1

This keeps only the rows from each user's most recent transaction date. All earlier dates are excluded.

Step 4: Counting Products per User

GROUP BY user_id, transaction_date

After filtering, each user has only rows from their latest date. GROUP BY collapses these into one row per user, and COUNT(product_id) tallies the number of products purchased on that day.

Step 5: Sorting Chronologically

ORDER BY transaction_date

The results are sorted by transaction_date in ascending order, showing users with earlier most-recent dates first.