Self-Joins and CTEs for Time Changes | Dataford Interview Questions

How do you use self-joins and CTEs to analyze time-based changes in sales data at Amplify?

Using the provided products and sales tables, write a PostgreSQL query that compares each product's monthly sales with its immediately preceding month's sales.

Output

One row per product-month with sales activity.
Columns: product_name, sales_month, current_sales, previous_sales, change_amount, and change_percent.
Include months without a matching prior month, using NULL comparison values where appropriate.
Sort by product_name, then sales_month ascending.

Schema

products

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

sales

Column	Type	Description
sale_idPK	INT	Unique sale identifier
product_id	INT	Referenced product identifier
sale_date	DATE	Date on which the sale occurred
amount	NUMERIC(12,2)	Sale amount
channel	VARCHAR(30)	Sales channel

Your question is Self-Joins and CTEs for Time Changes. Start with the requirements and the two tables in the Question tab.

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

Problem