Year-on-Year Growth Rate: Mastering the Must-Know Interview Question (Data Included!)
Hey everyone! Got an upcoming interview with a data-driven company? Cracking the year-on-year growth rate question can be a game-changer.

In this video, we'll break down the concept of YoY growth rate in a clear and concise way, making you feel confident when it pops up in your interview. We'll even walk you through a step-by-step approach to solve a sample question, complete with data!

Here's what you'll learn:

What is Year-on-Year Growth Rate (YoY)?
Why is it important in business analysis?
How to calculate YoY growth rate with a FORMULA (it's easier than you think!)
Practical Example: We'll tackle a sample interview question and guide you through the thought process, using real data for a more realistic experience.

By the end of this video, you'll be able to confidently explain YoY growth rate and impress your interviewer with your analytical skills.

Master more SQL Interview Question:
  / mastering-advanced-sql-practical-interview...  

Join us to master SQL & Python and elevate your data/business analytics expertise!

SQL Portfolio Project (Data Analyst or Business Analyst)
   • SQL For Data Analysis Full Portfolio ...  

Business Analyst Interview Question
   • Ace your Business Analyst Interview| ...  

SQL Order of Execution -
   • SQL Order of Execution | SQL Tutorial...  

Group BY & Having vs where in SQL
   • Where vs Having clause in SQL | GROUP...  

SQL Joins
   • SQL JOINS | INNER JOIN | LEFT JOIN | ...  

SQL Joins Interview Question
   • SQL Joins Vid #2  

CTE vs Temp Tables in SQL -
   • CTE vs Temp Tables in SQL| CTE |Tempo...  

Window Functions Explained
   • Window Functions in SQL | ROW NUMBER ...  

Let's connect:
Instagram -   / the_dataholics  
LinkedIN -   / posts  

Don't forget to like and subscribe for more interview prep tips!


Get the DDL commands and query below:
-- Y-on-Y Growth Rate [Wayfair SQL Interview Question]

use practice
-- SELECT * FROM user_transactions;

-- https://datalemur.com/questions/yoy-g...



CREATE TABLE user_transactions (
transaction_id INTEGER PRIMARY KEY,
product_id INTEGER,
spend DECIMAL(10, 2),
transaction_date DATETIME
);

INSERT INTO user_transactions
(transaction_id, product_id, spend, transaction_date) VALUES
(1, 101, 50.00, '2022-01-15'),
(2, 101, 75.00, '2022-02-20'),
(3, 102, 100.00, '2022-03-10'),
(4, 102, 150.00, '2023-01-12'),
(5, 103, 200.00, '2023-02-25'),
(6, 103, 250.00, '2022-05-30'),
(7, 104, 300.00, '2022-07-14'),
(8, 104, 350.00, '2023-06-18'),
(9, 105, 400.00, '2023-08-20'),
(10, 105, 450.00, '2022-09-25'),
(11, 106, 500.00, '2022-10-30'),
(12, 106, 550.00, '2023-11-10'),
(13, 107, 600.00, '2022-12-15'),
(14, 107, 650.00, '2023-04-20'),
(15, 108, 700.00, '2023-03-25'),
(16, 108, 750.00, '2022-11-30'),
(17, 109, 800.00, '2022-06-05'),
(18, 109, 850.00, '2023-05-10'),
(19, 110, 900.00, '2023-07-15'),
(20, 110, 950.00, '2022-08-20');



SELECT * FROM user_transactions;

/*
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.
*/

;
-- SQL SERVER Solution
with cte as (
select product_id, year(transaction_date) as year,
sum(spend) as Spent_Amount
from user_transactions
group by product_id, year(transaction_date)
-- order by product_id, year(transaction_date)
)
, cte1 as (
select year, product_id, spent_amount as curr_year_spend,
lag(spent_amount) over (partition by product_id order by year) as
prev_year_spend
from cte
)
-- select * from cte1
select *,
round(((curr_year_spend - prev_year_spend)/prev_year_spend) *100, 2)
as yoy_rate
from cte1

-- PostGRE SQL solution

with cte as (
select product_id, EXTRACT(YEAR FROM transaction_date) as year,
sum(spend) as Spent_Amount
from user_transactions
group by product_id, EXTRACT(YEAR FROM transaction_date)
order by product_id, EXTRACT(YEAR FROM transaction_date)
)
, cte1 as (
select year, product_id, spent_amount as curr_year_spend,
lag(spent_amount) over (partition by product_id order by year) as
prev_year_spend
from cte
)
select *,
round(((curr_year_spend - prev_year_spend)/prev_year_spend) *100, 2)
as yoy_rate
from cte1