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.
Output details
- Filter facts where
order_status equals Completed.
- Format known order months as
YYYY-MM, and label missing calendar dates as Unknown.
- Compute
completed_orders by counting every qualifying fact row without deduplication.
- Compute
total_revenue as the sum of quantity * unit_price, treating a NULL sum as 0.
- Sort
order_month and then product_category in ascending order.