Turn a year of raw orders into a monthly report, product and margin analysis, customer cohorts and RFM segments: 10 steps, real data, every query included.
Outcomes
The project
The first 2 steps are free to read. Buy it to unlock the rest.
A look at the code
Copy it and run it against the sample data.
CREATE TABLE products (
product_id text PRIMARY KEY,
product_name text NOT NULL,
category text NOT NULL,
unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0),
unit_cost numeric(12,2) NOT NULL CHECK (unit_cost >= 0)
);
CREATE TABLE customers (
customer_id text PRIMARY KEY,
signup_date date NOT NULL,
city text,
acquisition_channel text NOT NULL
);
CREATE TABLE orders (
order_id text PRIMARY KEY,
customer_id text NOT NULL REFERENCES customers (customer_id),
order_date date NOT NULL,
status text NOT NULL CHECK (status IN ('completed', 'cancelled', 'refunded'))
);
-- No primary key yet: step 2 removes duplicate lines first.
CREATE TABLE order_items (
order_id text NOT NULL REFERENCES orders (order_id),
product_id text NOT NULL REFERENCES products (product_id),
quantity integer NOT NULL CHECK (quantity > 0),
unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0)
);Questions
Any PostgreSQL 14 or newer works: installed locally, Docker, or a free hosted database such as Supabase or Neon. The queries use standard PostgreSQL features (window functions, FILTER, DATE_TRUNC).
Most queries work with small changes. The main differences are DATE_TRUNC (use DATE_FORMAT in MySQL or DATETRUNC in SQL Server 2022), the FILTER clause (use SUM(CASE WHEN ... THEN 1 ELSE 0 END)) and the duplicate-removal query in step 2.
It is a realistic simulated dataset built for learning: real-world patterns (growth, seasonality, a November discount, loyal and one-time customers) and deliberate quality problems, with no personal data about real people.
From customers who have Sales & Customer Analysis with SQL and Excel.
Code in data solutions is run against the sample data before publishing, and notes call out differences between databases.