logo

Back to questions

Histogram of Users and Purchases

Medium

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

Assume you are given the below table on transactions from users. Bucketing users based on their latest transaction date, write a query to obtain the number of users who made a purchase and the total number of products bought for each transaction date.

Output the transaction date, number of users and number of products.

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_datenumber_of_usersnumber_of_products
07/08/2022 12:00:0023
07/10/2022 12:00:0011

PostgreSQL 14

Refer friends to get bonus content & cool prizes.