SQL Joins and Year-Over-Year Growth | Dataford Interview Questions

Your question is SQL Joins and Year-Over-Year Growth. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Unity Technologies BI analysts need to monitor annual revenue across Unity products such as Unity Ads, Unity Cloud, and the Unity Asset Store. Write a PostgreSQL query that joins product metadata with revenue records and calculates year-over-year performance.

Requirements

Include revenue from 2023 through 2025 and aggregate it by product and calendar year.
Use a window function to show the previous available year's revenue for each product.
Calculate percentage growth only when the immediately preceding calendar year exists and its revenue is nonzero. Return NULL otherwise.
Return results ordered by product name and revenue year.

Schema

unity_products

Column	Type	Description
product_idPK	INT	Unique Unity product identifier
product_name	VARCHAR(100)	Unity product name
product_line	VARCHAR(50)	Product category

product_revenue

Column	Type	Description
revenue_idPK	INT	Unique revenue record identifier
product_id	INT	Referenced Unity product
revenue_date	DATE	Date revenue was recognized
net_revenue	DECIMAL(12,2)	Net revenue in USD