logo

Back to questions

Highest-Grossing Items

Medium

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

Assume you are given the table below containing information on Amazon customers and their spend on products belonging to various categories. Identify the top two highest-grossing products within each category in 2022. Output the category, product, and total spend.

Table:

Column NameType
categorystring
productstring
user_idinteger
spenddecimal
transaction_datetimestamp

Example Input:

categoryproductuser_idspendtransaction_date
appliancerefrigerator165246.0012/26/2021 12:00:00
appliancerefrigerator123299.9903/02/2022 12:00:00
appliancewashing machine123219.8003/02/2022 12:00:00
electronicsvacuum178152.0004/05/2022 12:00:00
electronicswireless headset156249.9007/08/2022 12:00:00
electronicsvacuum145189.0007/15/2022 12:00:00

Example Output:

categoryproducttotal_spend
appliancerefrigerator299.99
appliancewashing machine219.80
electronicsvacuum341.00
electronicswireless headset249.90

PostgreSQL 14

Refer friends to get bonus content & cool prizes.