Schema Reference

Standard retail schema used across all contributions. Use these exact table and column names in your submissions.

Tables

Table	Type	Key Columns
fact_sales	Fact	sale_id, customer_id, product_id, store_id, date_id, net_sales, quantity, is_return
dim_product	Dimension	product_id, product_name, category, sub_category, brand, unit_cost
dim_customer	Dimension	customer_id, customer_segment, acquisition_channel, region
dim_store	Dimension	store_id, store_name, city, region, opening_date
dim_date	Dimension	date_id, full_date, week, month, quarter, year

Common Join Patterns

-- Sales with product and date
FROM fact_sales s
JOIN dim_product p ON s.product_id = p.product_id
JOIN dim_date d    ON s.date_id    = d.date_id

-- Sales with store
FROM fact_sales s
JOIN dim_store st  ON s.store_id = st.store_id

-- Full star join
FROM fact_sales s
JOIN dim_product  p  ON s.product_id  = p.product_id
JOIN dim_customer c  ON s.customer_id = c.customer_id
JOIN dim_store    st ON s.store_id    = st.store_id
JOIN dim_date     d  ON s.date_id     = d.date_id

Common Filters

-- Exclude returns
WHERE s.is_return = FALSE

-- Rolling 12-month window
AND d.full_date >= CURRENT_DATE - INTERVAL '12 months'

-- Rolling 90-day window
AND d.full_date >= CURRENT_DATE - INTERVAL '90 days'

-- Specific quarter and year
AND d.year = 2024 AND d.quarter = 4

-- Exclude newly opened stores
AND st.opening_date <= CURRENT_DATE - INTERVAL '90 days'