Star schemas: facts, dimensions and SCD type 2

-- The source: the practice shop's tables, with the day of each order added.
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE products (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL,
                       price_paise INTEGER NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, ordered_on TEXT NOT NULL,
                     status TEXT NOT NULL);
CREATE TABLE order_items (order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL,
                          PRIMARY KEY (order_id, product_id));
INSERT INTO customers VALUES
  (1, 'Asha Kulkarni', 'Pune'), (2, 'Vikram Nair', 'Kochi'), (3, 'Meera Rathore', 'Jaipur'), (4, 'Tenzin Dorjee', 'Gangtok');
INSERT INTO products VALUES
  (1, 'Basmati rice 1 kg', 'Grains', 14999), (2, 'Toor dal 500 g', 'Pulses', 8950),
  (3, 'Masala chai 250 g', 'Beverages', 12000), (4, 'Coconut oil 1 L', 'Oils', 21500), (5, 'Ragi flour 1 kg', 'Grains', 6800);
INSERT INTO orders VALUES
  (101, 1, '2026-04-03', 'delivered'), (102, 2, '2026-04-11', 'delivered'), (103, 1, '2026-04-28', 'delivered'),
  (104, 3, '2026-05-02', 'cancelled'), (105, 4, '2026-05-09', 'delivered'), (106, 2, '2026-05-20', 'delivered');
INSERT INTO order_items VALUES
  (101, 1, 2), (101, 2, 1), (102, 4, 1), (103, 3, 2), (103, 5, 1), (104, 2, 3), (105, 1, 1), (105, 3, 1), (106, 5, 4);

-- The star. Grain of fact_sales: one row per product line of a delivered order.
CREATE TABLE dim_date (date_key INTEGER PRIMARY KEY, full_date TEXT NOT NULL, month TEXT NOT NULL,
                       fy_quarter TEXT NOT NULL);
CREATE TABLE dim_customer (customer_key INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, city TEXT NOT NULL);
CREATE TABLE dim_product (product_key INTEGER PRIMARY KEY, product_id INTEGER NOT NULL, category TEXT NOT NULL);
CREATE TABLE fact_sales (
  date_key     INTEGER NOT NULL REFERENCES dim_date (date_key),
  customer_key INTEGER NOT NULL REFERENCES dim_customer (customer_key),
  product_key  INTEGER NOT NULL REFERENCES dim_product (product_key),
  order_id     INTEGER NOT NULL,       -- kept for drilling down to the order
  quantity     INTEGER NOT NULL,
  amount_paise INTEGER NOT NULL
);

-- Every day of April and May, keyed as the number yyyymmdd.
INSERT INTO dim_date
WITH RECURSIVE days (d) AS (
  SELECT '2026-04-01' UNION ALL SELECT date(d, '+1 day') FROM days WHERE d < '2026-05-31'
)
SELECT cast(strftime('%Y%m%d', d) AS INTEGER), d, strftime('%Y-%m', d),
       printf('FY%d-%02d Q%d', y - (m < 4), (y - (m < 4) + 1) % 100, (m + 8) % 12 / 3 + 1)
FROM (SELECT d, cast(strftime('%Y', d) AS INTEGER) AS y, cast(strftime('%m', d) AS INTEGER) AS m FROM days);
INSERT INTO dim_customer (customer_id, city) SELECT customer_id, city FROM customers;
INSERT INTO dim_product (product_id, category) SELECT product_id, category FROM products;
INSERT INTO fact_sales
SELECT cast(strftime('%Y%m%d', o.ordered_on) AS INTEGER), c.customer_key, p.product_key, o.order_id,
       i.quantity, i.quantity * s.price_paise
FROM orders AS o
JOIN order_items AS i  ON i.order_id = o.order_id
JOIN products AS s     ON s.product_id = i.product_id
JOIN dim_customer AS c ON c.customer_id = o.customer_id
JOIN dim_product AS p  ON p.product_id = i.product_id
WHERE o.status = 'delivered';

SELECT count(*) AS fact_rows, sum(quantity) AS units, sum(amount_paise) / 100.0 AS revenue_rupees FROM fact_sales;

-- A star join: the fact, two of its dimensions, and a GROUP BY on their columns.
SELECT d.month, p.category, sum(f.quantity) AS units, sum(f.amount_paise) / 100.0 AS revenue_rupees
FROM fact_sales AS f
JOIN dim_date    AS d ON d.date_key = f.date_key
JOIN dim_product AS p ON p.product_key = f.product_key
GROUP BY d.month, p.category
ORDER BY d.month, p.category;

┌───────────┬───────┬────────────────┐
│ fact_rows │ units │ revenue_rupees │
├───────────┼───────┼────────────────┤
│ 8         │ 13    │ 1454.47        │
└───────────┴───────┴────────────────┘
┌─────────┬───────────┬───────┬────────────────┐
│  month  │ category  │ units │ revenue_rupees │
├─────────┼───────────┼───────┼────────────────┤
│ 2026-04 │ Beverages │ 2     │ 240.0          │
│ 2026-04 │ Grains    │ 3     │ 367.98         │
│ 2026-04 │ Oils      │ 1     │ 215.0          │
│ 2026-04 │ Pulses    │ 1     │ 89.5           │
│ 2026-05 │ Beverages │ 1     │ 120.0          │
│ 2026-05 │ Grains    │ 5     │ 421.99         │
└─────────┴───────────┴───────┴────────────────┘