logo

Back to questions

Y-on-Y Growth Rate [Wayfair SQL Interview Question]

Hard

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

Assume you are given the table below containing information on user transactions for particular products. Write a query to obtain the year-on-year growth rate for the total spend of each product for each year.

Output the year (in ascending order) partitioned by product id, current year's spend, previous year's spend and year-on-year growth rate (percentage rounded to 2 decimal places).

Table:

Column NameType
transaction_idinteger
product_idinteger
spenddecimal
transaction_datedatetime

Example Input:

transaction_idproduct_idspendtransaction_date
13411234241500.6012/31/2019 12:00:00
14231234241000.2012/31/2020 12:00:00
16231234241246.4412/31/2021 12:00:00
13221234242145.3212/31/2022 12:00:00

Example Output:

yearproduct_idcurr_year_spendprev_year_spendyoy_rate
20191234241500.60
20201234241000.201500.60-33.35
20211234241246.441000.2024.62
20221234242145.321246.4472.12

The third row in the example output shows that the spend for product 123424 grew 24.62% from 1000.20 in 2020 to 1246.44 in 2021.

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

PostgreSQL 14