Rolling GM% With Late-Arriving Facts | Dataford Interview Questions

NVIDIA finance analysts need a daily gross-margin view for NVIDIA products by sales region. Write a PostgreSQL query using fact_sales and dim_calendar to calculate a 28-day rolling GM% that remains correct when a sale arrives late or a previously loaded sale is corrected.

Requirements

Treat the row with the greatest loaded_at as the current version of each sale_id.
Use dim_calendar as the reporting date spine, including dates with no sales.
Aggregate revenue and COGS by calendar date, region, and product before applying the rolling calculation.
Calculate rolling revenue, rolling COGS, and gm_percent = (revenue - cogs) / revenue * 100, rounded to two decimals.
Return results for every region and product represented by a valid sale, ordered by region, product, and calendar date.

Schema

fact_sales

Column	Type	Description
sale_id	BIGINT	Business identifier shared by all versions of a sale
sale_date	DATE	Economic date of the sale, related to dim_calendar.calendar_date
loaded_at	TIMESTAMP	Timestamp when this fact version entered the warehouse
region	VARCHAR(40)	Sales region
product	VARCHAR(80)	NVIDIA product family
revenue	NUMERIC(14,2)	Sale revenue
cogs	NUMERIC(14,2)	Cost of goods sold

dim_calendar

Column	Type	Description
calendar_datePK	DATE	Calendar date used as the reporting date spine

Your question is Rolling GM% With Late-Arriving Facts. 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