Walmart SQL Interview Question with Step-by-Step Solution | Real Interview Experience

SQL interviews aren’t just about syntax — they’re about thinking in steps.”

In one of my recent mock interview prep sessions, I came across a real SQL interview question from Walmart that perfectly tests your understanding of window functions, grouping, and chronological ordering. It’s simple on the surface but packs a punch when you break it down.

Let’s walk through it step by step, with a clear solution and reasoning behind each part.

You’re given a table of user transactions from Walmart. The table contains:

• product_id: The ID of the product purchased
• user_id: The ID of the user
• spend: Amount spent
• transaction_date: Timestamp of the purchase

Your task: For each user, find the most recent transaction date and count how many products they purchased in that transaction.

Output the results as:

• transaction_date
• user_id
• purchase_count

And make sure the results are sorted in chronological order.

Solution Strategy

The problem may look straightforward, but it requires you to think in three logical steps:

1. Identify each user’s most recent transaction
2. Count the number of products bought in that transaction
3. Sort the results chronologically.

Let’s look at two different methods to solve this.

Want to watch a hands-on walkthrough this SQL interview problems? Check out the following video for better explanation.

[embed]

📌 Method 1: Using RANK() Window Function

Step 1: Rank Transactions by Date

We’ll rank each transaction per user using RANK() ordered by transaction_date DESC so that the most recent transaction gets a rank of 1.

SELECT 
  transaction_date, 
  user_id, 
  product_id, 
  RANK() OVER (
    PARTITION BY user_id 
    ORDER BY transaction_date DESC
  ) AS transaction_rank
FROM user_transactions;

This gives us every transaction with a rank. We’ll focus only on rank = 1.

Step 2: Filter Latest Transactions

Let’s wrap the above query in a CTE and keep only the top-ranked transactions per user.

WITH latest_transactions_cte AS (
  SELECT 
    transaction_date, 
    user_id, 
    product_id, 
    RANK() OVER (
      PARTITION BY user_id 
      ORDER BY transaction_date DESC
    ) AS transaction_rank
  FROM user_transactions
)
SELECT *
FROM latest_transactions_cte
WHERE transaction_rank = 1;

Now we have each user’s latest transaction(s). Remember, a user can have multiple products in the same transaction timestamp.

Step 3: Count Products and Sort Chronologically

Now we count the products per user on their latest transaction date and sort by date.

WITH latest_transactions_cte AS (
  SELECT 
    transaction_date, 
    user_id, 
    product_id, 
    RANK() OVER (
      PARTITION BY user_id 
      ORDER BY transaction_date DESC
    ) AS transaction_rank
  FROM user_transactions
)
SELECT 
  transaction_date, 
  user_id,
  COUNT(product_id) AS purchase_count
FROM latest_transactions_cte
WHERE transaction_rank = 1
GROUP BY transaction_date, user_id
ORDER BY transaction_date;

Output Explanation

User 123’s latest transaction was on 07/11/2022 with 1 product. User 115 bought 1 item on 07/12/2022. User 159 bought 2 products on 07/12/2022.

📌Method 2: Using MAX() + Join (More Intuitive for Beginners)

If you prefer a more intuitive approach without window functions, here’s an alternative that works just as well.

Step 1: Get Latest Transaction Date per User

WITH recent_transactions AS (
    SELECT
        user_id,
        MAX(transaction_date) AS recent_date
    FROM
        user_transactions
    GROUP BY
        user_id
)

This gives us each user’s most recent purchase date.

Step 2: Join with Original Table & Count Products

SELECT
    ut.transaction_date,
    ut.user_id,
    COUNT(ut.product_id) AS purchase_count
FROM
    user_transactions ut
JOIN
    recent_transactions rt
ON
    ut.user_id = rt.user_id
    AND ut.transaction_date = rt.recent_date
GROUP BY
    ut.transaction_date, ut.user_id
ORDER BY
    ut.transaction_date ASC;

🎯 Key Learnings

• Use RANK() or MAX() depending on what you're more comfortable with.
• Always group by the right fields when counting.
• Don’t forget to sort results if the question explicitly asks for it!

This Walmart interview question is a perfect example of real-world data reasoning:

• You’re given noisy, granular data.
• You’re asked to filter, group, and summarize meaningfully.
• You’re expected to write clear and efficient SQL that does all of this.

“If you found this helpful, don’t forget to give it a few claps 👏 and follow for more real-world SQL interview insights and data tips!