Fact Table

`
-- Fact table: Sales transactions

CREATE TABLE fct_sales (
  -- Surrogate key (optional but common)
  sale_id BIGINT PRIMARY KEY,
  
  -- Foreign keys to dimensions
  date_id INT NOT NULL,
  product_id INT NOT NULL,
  customer_id INT NOT NULL,
  store_id INT NOT NULL,
  
  -- Measures (numeric, aggregatable values)
  revenue DECIMAL(10, 2),
  quantity INT,
  discount_amount DECIMAL(10, 2),
  tax_amount DECIMAL(10, 2),
  
  -- Optional: Slowly changing dimension keys for history
  product_key INT,  -- Current product reference
  product_history_key INT  -- Historical product version
  
  -- Indexes for joins
  FOREIGN KEY (date_id) REFERENCES dim_date,
  FOREIGN KEY (product_id) REFERENCES dim_product,
  FOREIGN KEY (customer_id) REFERENCES dim_customer,
  FOREIGN KEY (store_id) REFERENCES dim_store
);

CREATE INDEX idx_fct_sales_date ON fct_sales(date_id);
CREATE INDEX idx_fct_sales_product ON fct_sales(product_id);
CREATE INDEX idx_fct_sales_customer ON fct_sales(customer_id);
CREATE INDEX idx_fct_sales_store ON fct_sales(store_id);

-- Analytical queries aggregate fact measures by dimension attributes

-- Query 1: Total revenue by product
SELECT 
  p.category,
  p.product_name,
  SUM(f.revenue) as total_revenue,
  SUM(f.quantity) as units_sold
FROM fct_sales f
JOIN dim_product p ON f.product_id = p.product_id
GROUP BY p.category, p.product_name
ORDER BY total_revenue DESC;

-- Query 2: Revenue trend over time
SELECT 
  d.year,
  d.month,
  SUM(f.revenue) as monthly_revenue
FROM fct_sales f
JOIN dim_date d ON f.date_id = d.date_id
GROUP BY d.year, d.month
ORDER BY d.year, d.month;

-- Query 3: Multi-dimensional analysis
SELECT 
  d.year,
  c.region,
  p.category,
  SUM(f.revenue) as region_category_revenue,
  COUNT(*) as transaction_count
FROM fct_sales f
JOIN dim_date d ON f.date_id = d.date_id
JOIN dim_customer c ON f.customer_id = c.customer_id
JOIN dim_product p ON f.product_id = p.product_id
WHERE d.year = 2024
GROUP BY d.year, c.region, p.category;
`