Fact and Dimension Tables | Dataford Interview Questions

What is Fact and Dimensions tables?

Asked in the technical screen stage. Data warehousing concept. Write a SQL query using the provided fact and dimension tables to summarize completed order facts.

Output

One row per calendar month and product category.
Columns: order_month, product_category, completed_orders, and total_revenue.
Include completed facts even when a dimension lookup is missing, labeling missing dimension values as Unknown.
Sort by order_month, then product_category.

Schema

fact_orders

Column	Type	Description
order_fact_idPK	INT	Unique order fact identifier
order_date_id	INT	Reference to the calendar date dimension
product_id	INT	Reference to the product dimension
quantity	INT	Number of units in the order fact
unit_price	NUMERIC(10,2)	Price per unit
order_status	VARCHAR(20)	Order lifecycle status

dim_date

Column	Type	Description
date_idPK	INT	Surrogate key for the date dimension
calendar_date	DATE	Calendar date
month_name	VARCHAR(20)	Calendar month name

dim_product

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

Your question is Fact and Dimension Tables. 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