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're given a table containing information about Wayfair user transactions for different products. Write a query to calculate the year-on-year growth rate for the total spend of each product, grouping the results by product ID.

The output should include the year in ascending order, product ID, current year's spend, previous year's spend and year-on-year growth 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.60NULLNULL
20201234241000.201500.60-33.35
20211234241246.441000.2024.62
20221234242145.321246.4472.12

Explanation:

Product ID 123424 is analyzed for multiple years: 2019, 2020, 2021, and 2022.

• In the year 2020, the current year's spend is 1000.20, and there is no previous year's spend recorded (indicated by an empty cell).
• In the year 2021, the current year's spend is 1246.44, and the previous year's spend is 1000.20.
• In the year 2022, the current year's spend is 2145.32, and the previous year's spend is 1246.44.

To calculate the year-on-year growth rate, we compare the current year's spend with the previous year's spend.For instance, the spend grew by 24.62% from 2020 to 2021, indicating a positive growth rate.

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

PostgreSQL 14