Y-on-Y Growth Rate
ASKED IN INTERVIEWWayfair
Problem Statement
Calculate the year-over-year revenue growth for each product.
Rules:
- Output columns: product_id, year, current_year_revenue, previous_year_revenue, yoy_growth_percentage.
- Revenue = sum of (unit_price * quantity).
- Growth = ((Current - Previous) / Previous) * 100.
- Round percentages to 2 decimal places.
- Sort by product_id ASC and year ASC.
Tests your understanding of
LAG, Window Functions, Aggregation and Date Extraction
Input Tables
products
| product_id(INTEGER) | product_name(VARCHAR) | unit_price(DECIMAL) |
|---|---|---|
| 1 | Velvet Sofa | 500 |
| 2 | Oak Desk | 200 |
orders
| order_id(INTEGER) | product_id(INTEGER) | quantity(INTEGER) | order_date(DATE) |
|---|---|---|---|
| 101 | 1 | 2 | 2023-05-10 |
| 102 | 1 | 3 | 2024-06-15 |
| 103 | 2 | 5 | 2023-01-01 |
| 104 | 2 | 10 | 2024-01-01 |
product_categories
| product_id(INTEGER) | category_name(VARCHAR) |
|---|---|
| 1 | Living Room |
| 2 | Office |
Expected Output
| product_id(INTEGER) | year(INTEGER) | current_year_revenue(DECIMAL) | previous_year_revenue(DECIMAL) | yoy_growth_percentage(DECIMAL) |
|---|---|---|---|---|
| 1 | 2023 | 1000 | null | null |
| 1 | 2024 | 1500 | 1000 | 50 |
| 2 | 2023 | 1000 | null | null |
| 2 | 2024 | 2000 | 1000 | 100 |
Tags
HardASKED IN INTERVIEWLAGWindow FunctionsAggregationDate Extraction
25-30 min
32%