Star Schema and Facts | Dataford Interview Questions

Explain how you would model sales transactions using a star schema with a fact table and dimension tables.

Use the provided sales fact, product dimension, and date dimension structures. Write a PostgreSQL query that demonstrates the model by summarizing completed sales from the first quarter of 2025.

Output

One row per calendar month and product category.
Return month_start, category, transaction_count, total_quantity, and total_sales.
Include unmatched products as Unknown, exclude canceled sales and dates outside the quarter, and sort by month_start then category.

Schema

fact_sales

Column	Type	Description
sale_idPK	INT	Unique sales transaction identifier
date_key	INT	Reference to the date dimension
product_key	INT	Reference to the product dimension
quantity	INT	Number of units sold
unit_price	DECIMAL(10,2)	Price per unit at the time of sale
sales_status	VARCHAR(20)	Transaction status

dim_product

Column	Type	Description
product_keyPK	INT	Surrogate product dimension key
product_name	VARCHAR(100)	Product name
category	VARCHAR(50)	Product category

dim_date

Column	Type	Description
date_keyPK	INT	Surrogate date dimension key
calendar_date	DATE	Calendar date

Your question is Star Schema and Facts. Start with the requirements and the three 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