User Purchase Hist.
ASKED IN INTERVIEWWalmart
Problem Statement
Walmart wants to analyze recency of shopping behavior. You need to identify how many users were active on their latest recorded purchase date in the system.
Write an SQL query to find the count of users who made at least one purchase on the latest date that appears for them in the purchase history.
Rules:
- The latest date is unique to each user.
- Only return the final count of these users.
- Output column: user_count.
- Result should be an integer.
Tests your understanding of
Dates, Filtering, Aggregation and Subqueries
Input Tables
users
| user_id(INTEGER) | user_name(VARCHAR) |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
| 4 | David |
products
| product_id(INTEGER) | product_name(VARCHAR) |
|---|---|
| 501 | Groceries |
| 502 | Electronics |
purchases
| purchase_id(INTEGER) | user_id(INTEGER) | product_id(INTEGER) | purchase_date(TIMESTAMP) |
|---|---|---|---|
| 1 | 1 | 501 | 2023-01-01 10:00:00 |
| 2 | 1 | 502 | 2023-01-05 12:00:00 |
| 3 | 2 | 501 | 2023-01-02 09:00:00 |
| 4 | 2 | 502 | 2023-01-02 15:00:00 |
| 5 | 3 | 501 | 2023-01-10 08:00:00 |
| 6 | 4 | 502 | 2023-01-01 10:00:00 |
Expected Output
| user_count(INTEGER) |
|---|
| 4 |
Tags
MediumASKED IN INTERVIEWDatesFilteringAggregationSubqueries
15-20 min
52%