logo

Back to questions

Highest-Grossing Items [Amazon SQL Interview Question]

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 containing information on Amazon customers and their spending on products in 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

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

PostgreSQL 14