Back to questions
This is the same question as problem #13 in the SQL Chapter of Ace the Data Science Interview!
Assume you're given a table on Walmart user transactions. Based on their most recent transaction date, write a query that retrieve the users along with the number of products they bought.
Output the user's most recent transaction date, user ID, and the number of products, sorted in chronological order by the transaction date.
Starting from November 10th, 2022, the official solution was updated, and the expected output of transaction date, number of users, and number of products was changed to the current expected output.
The dataset you are querying against may have different input & output - this is just an example!
By partitioning the data by user ID and ordering it by transaction date in descending order, we assign a rank to each transaction date. The rank 1 represents the latest transaction date.
Next, we filter the records to include only the transactions with the highest rank (rank 1). This ensures that we retain only the latest transaction for each user.
In this step, we select the transaction date, user ID, and product ID from the filtered records. Additionally, we include a count of the product IDs to determine the number of products purchased in each transaction.