logo

Back to questions

Histogram of Users and Purchases [Walmart SQL Interview Question]

Medium

This is the same question as problem #13 in the SQL Chapter of Ace the Data Science Interview!

Assume you are given the table on Walmart user transactions. Based on a user's most recent transaction date, write a query to obtain the users and the number of products bought.

Output the user's most recent transaction date, user ID and the number of products sorted by the transaction date in chronological order.

P.S. As of 10 Nov 2022, the official solution was changed from output of the transaction date, number of users and number of products to the current output.

Table:

Column NameType
product_idinteger
user_idinteger
spenddecimal
transaction_datetimestamp

Example Input:

product_iduser_idspendtransaction_date
367312368.9007/08/2022 12:00:00
9623123274.1007/08/2022 12:00:00
146711519.9007/08/2022 12:00:00
251315925.0007/08/2022 12:00:00
145215974.5007/10/2022 12:00:00

Example Output:

transaction_dateuser_idpurchase_count
07/08/2022 12:00:001151
07/08/2022 12:00:0001232
07/10/2022 12:00:001591

The dataset you are querying against may have different input & output - this is just an example!

PostgreSQL 14