A self-contained PostgreSQL analytics toolkit built around a fictional
e-commerce store. It ships a normalized schema, a substantial seed dataset, a
set of reusable reporting views, and a library of well-commented advanced
analytical SQL queries — window functions, recursive CTEs, cohort/retention,
RFM segmentation, top-N-per-group, moving averages, pivots with FILTER, and a
conversion funnel.
Everything runs with one command via Docker; no local Postgres required.
sql-analytics-toolkit/
├── schema/
│ ├── 01_schema.sql # tables, keys, constraints, indexes, comments
│ ├── 02_seed.sql # reproducible seed data (generate_series based)
│ └── 03_views.sql # reusable reporting views
├── queries/ # one analytical theme per file (see catalogue below)
├── docker-compose.yml # postgres:16, auto-loads schema/ on first boot
├── scripts/
│ ├── run.sh # up + wait + run a demo query
│ └── psql.sh # open an interactive psql shell
├── Makefile # up / down / load / demo / psql / clean
└── LICENSE # MIT
Eight tables in third normal form. categories is self-referencing (a category
may have a parent), giving a small hierarchy to walk with a recursive CTE.
categories ─┐ (parent_id ─┐)
▲ └────self─────┘
│ category_id
│
products ──1:1── inventory
▲
│ product_id
│
order_items ──▶ orders ──▶ customers
│ │ ▲ ▲
│ │ │ │ customer_id
│ │ └── payments (1:N)
│ │
│ └── order_ts, status, ship_country
│
└── reviews ──▶ products, customers
Relationships
─────────────
customers 1 ─── N orders
orders 1 ─── N order_items (CASCADE on order delete)
orders 1 ─── N payments (CASCADE on order delete)
products 1 ─── N order_items
products 1 ─── 1 inventory
products 1 ─── N reviews
customers 1 ─── N reviews
categories 1 ─── N products
categories 1 ─── N categories (parent_id, self-reference)
Design highlights: surrogate identity PKs, foreign keys with explicit
ON DELETE behaviour, CHECK constraints for money/enum domains, NUMERIC
money columns (never FLOAT), a UNIQUE (order_id, product_id) guard on line
items, and indexes on every foreign key plus common filter/sort columns.
| View | Purpose |
|---|---|
order_totals |
Net value + item count per order (single source of truth) |
monthly_revenue |
Recognised revenue / active customers per month |
customer_lifetime_value |
Per-customer paid spend, order count, and recency |
product_performance |
Units, revenue, and rating per product (fan-out safe) |
Requires Docker with the Compose plugin (docker compose) or the legacy
docker-compose binary.
make up # start postgres:16 and auto-load schema/ (01 → 02 → 03)
make demo # run the default demo query (RFM segmentation)
make psql # drop into an interactive psql shell
make down # stop the container (keeps data)
make clean # stop and delete the data volumeRun any specific query:
make demo QUERY=queries/05_cohort_retention.sql
# or, without make:
./scripts/run.sh queries/08_funnel_conversion.sqlThe database listens on localhost:5433 (db=shop, user=analytics,
password=analytics). The schema directory is mounted into
/docker-entrypoint-initdb.d, so Postgres executes the three files in order on
first boot.
Each file starts with a comment header stating the business question and the SQL techniques it demonstrates.
| # | File | Business question | Key techniques |
|---|---|---|---|
| 01 | 01_running_totals_moving_avg.sql |
How has cumulative revenue grown, and what is a smoothed 3-month trend? | SUM/AVG window frames, ROWS BETWEEN |
| 02 | 02_top_n_per_group.sql |
Which are the top 3 products by revenue in each category? | RANK, DENSE_RANK, PARTITION BY, top-N |
| 03 | 03_month_over_month_growth.sql |
What is each month's revenue vs. the previous/next month? | LAG, LEAD, growth %, NULLIF |
| 04 | 04_recursive_category_tree.sql |
What does the full category hierarchy look like? | WITH RECURSIVE, depth + materialised path |
| 05 | 05_cohort_retention.sql |
How well does each signup cohort keep ordering over time? | multi-CTE pipeline, age(), retention % |
| 06 | 06_rfm_segmentation.sql |
How do we segment customers by Recency/Frequency/Monetary value? | NTILE(5) quintiles, CASE segmentation |
| 07 | 07_pivot_with_filter.sql |
How is revenue split by category across order statuses? | aggregate FILTER (WHERE ...) pivot |
| 08 | 08_funnel_conversion.sql |
What fraction of orders progress Placed → Paid → Shipped → Delivered? | FILTER, LATERAL VALUES, LAG, first_value |
Fulfilment funnel (queries/08_funnel_conversion.sql):
stage_name | order_count | prev_stage_count | step_conversion_pct | overall_pct
------------+-------------+------------------+---------------------+-------------
Placed | 518 | | | 100.0
Paid | 432 | 518 | 83.4 | 83.4
Shipped | 293 | 432 | 67.8 | 56.6
Delivered | 230 | 293 | 78.5 | 44.4
Running total and moving average (queries/01_running_totals_moving_avg.sql):
month | revenue | running_total | moving_avg_3m
------------+----------+---------------+---------------
2024-03-01 | 29440.25 | 29440.25 | 29440.25
2024-04-01 | 23497.74 | 52937.99 | 26469.00
2024-05-01 | 38388.81 | 91326.80 | 30442.27
2024-06-01 | 31169.82 | 122496.62 | 31018.79
RFM segmentation (queries/06_rfm_segmentation.sql):
customer_id | full_name | recency_days | frequency | monetary | r | f | m | segment
-------------+--------------+--------------+-----------+----------+---+---+---+-----------
110 | Mateo Garcia | 9 | 5 | 15558.32 | 5 | 5 | 5 | Champions
200 | Ava Silva | 4 | 6 | 14257.27 | 5 | 5 | 5 | Champions
169 | Aria Jones | 351 | 6 | 17116.63 | 2 | 5 | 5 | At Risk
Exact numbers depend on the seeded random data; the seed is fixed with
setseed()so a fresh load is reproducible.
MIT — see LICENSE. Copyright (c) 2026 mooceanstudio.