Capstone: A Sales Analytics Database
Turn a busy transactional shop database into a fast, query-friendly analytics layer
Big Idea: Reporting queries and order-taking queries want opposite things. In this final project you keep the original OLTP tables for day-to-day writes, and build a separate star-schema analytics layer on top of them: a wide
fact_sales table surrounded by small dimension tables. You load that layer with a single INSERT ... SELECT, drive dashboards with window functions, and make it fast with the right indexes — checking every choice with EXPLAIN.
By the end you will be able to:
- design a star schema (fact + dimensions) from an existing OLTP model,
- load the fact table from normalised source tables with one join query,
- build ranking, running-total and cohort dashboards with window functions,
- choose composite indexes for the fact table and confirm them with
EXPLAIN, - describe the honest trade-offs of a denormalised analytics copy.
1. The source: our OLTP shop
Everything starts from the normalised operational database you already know how to build. It has four tables: customers place orders, each order has line items, and each line item points at a product.
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(80),
country VARCHAR(40),
signup_date DATE
);
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(80),
category VARCHAR(40),
price DECIMAL(10,2)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id),
order_date DATE,
status VARCHAR(20)
);
CREATE TABLE order_items (
order_id INT REFERENCES orders(order_id),
product_id INT REFERENCES products(product_id),
quantity INT,
unit_price DECIMAL(10,2),
PRIMARY KEY (order_id, product_id)
);A few rows to make the queries concrete:
INSERT INTO customers VALUES
(1,'Aida','KG','2024-01-05'),
(2,'Bekzat','KZ','2024-02-11'),
(3,'Chris','US','2024-02-20');
INSERT INTO products VALUES
(10,'Keyboard','Peripherals',25.00),
(11,'Mouse','Peripherals',15.00),
(12,'Monitor','Displays',180.00);
INSERT INTO orders VALUES
(100,1,'2024-03-01','paid'),
(101,2,'2024-03-02','paid'),
(102,1,'2024-03-15','paid');
INSERT INTO order_items VALUES
(100,10,2,25.00),(100,11,1,15.00),
(101,12,1,180.00),
(102,11,3,15.00),(102,12,1,180.00);This design is perfect for writing orders: no duplicated data, tidy foreign keys. But a report like "monthly revenue per category, ranked" has to join four tables every single time. That is where an analytics layer earns its keep.