SynthForge relational sample pack
=================================

Fully synthetic multi-table retail practice database.
No real people. No real customers. For SQL practice and demos only.

Person names follow US Census (surnames) and SSA (given names) frequency
distributions. First names are consistent with birth year and gender on
the same row when those columns are present.

Generated with fixed seed 20260808 by SynthForge
(https://synthforge.io). Greenfield only; SynthForge does not ingest
or de-identify real data.

Tables
------
customers, products, orders, order_items
Foreign keys: orders.customer_id -> customers,
  order_items.order_id -> orders, order_items.product_id -> products.

Load (from the extracted directory so csv/ paths resolve)
---------------------------------------------------------
  duckdb demo.duckdb < import_duckdb.sql
  psql -d demo -f import_postgresql.sql
  mysql -u user -p --local-infile=1 demo < import_mysql.sql

INTERESTING SQL
---------------
Run after load. Orphan checks should return 0.

-- orphan_orders
SELECT COUNT(*) AS orphan_orders
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;
-- expect 0

-- orphan_items_order
SELECT COUNT(*) AS orphan_items
FROM order_items i
LEFT JOIN orders o ON i.order_id = o.order_id
WHERE o.order_id IS NULL;
-- expect 0

-- orphan_items_product
SELECT COUNT(*) AS orphan_items
FROM order_items i
LEFT JOIN products p ON i.product_id = p.product_id
WHERE p.product_id IS NULL;
-- expect 0

-- surname_top
SELECT last_name, COUNT(*) AS n,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 3) AS pct
FROM customers
GROUP BY last_name
ORDER BY n DESC
LIMIT 10;
-- Smith should be near 0.92% of rows (Census 2010)

-- cohort_1950s
SELECT first_name, COUNT(*) AS n
FROM customers
WHERE EXTRACT(YEAR FROM CAST(date_of_birth AS DATE)) BETWEEN 1950 AND 1959
GROUP BY first_name
ORDER BY n DESC
LIMIT 10;
-- expect Mary / Linda / Patricia era names among women

-- cohort_2010s
SELECT first_name, COUNT(*) AS n
FROM customers
WHERE EXTRACT(YEAR FROM CAST(date_of_birth AS DATE)) BETWEEN 2010 AND 2015
GROUP BY first_name
ORDER BY n DESC
LIMIT 10;
-- expect Emma / Olivia / Sophia era names among women

-- customer_spend
SELECT c.customer_id, c.first_name, c.last_name,
       ROUND(SUM(i.line_price), 2) AS total_spend,
       COUNT(DISTINCT o.order_id) AS orders
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items i ON i.order_id = o.order_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spend DESC
LIMIT 15;

License: use freely for practice and demos (synthetic data).
