Most data analysis demos stop at the SQL. There is a nice query, a table of results, and then a gap where the actual deliverable should be. I wanted the whole path in one place: load the data, run the queries, chart the results, and produce a report a non-technical person could actually read.
So I built data-analysis-demo around exactly that path.
The data
To keep the repo self-contained, the dataset is generated in Python. 200 customers, 50 products, and 1,000 orders, with order statuses split roughly 70% Completed, 20% Shipped, 10% Processing, all loaded into SQLite with pandas.to_sql. A --data flag accepts a CSV if you want to bring your own.
I put the fact that it is synthetic in the README, up front. The revenue numbers themselves mean nothing. What the project demonstrates is the pipeline, and that anyone can clone it, run one command, and get the same shape of output.
Four analyses, one function each
Each analysis is a single function: a SQL query through pd.read_sql, then a chart saved to disk, then the DataFrame returned to the caller.
Revenue by category. A three-way join over orders, order_items and products, grouped by category, drawn as a labelled horizontal bar chart.
Customer lifetime value. A CTE computes per-customer order counts and lifetime spend with a LEFT JOIN, so customers with zero orders still survive the join, then groups the result by segment.
Monthly trends.
strftime('%Y-%m', order_date)buckets orders by month, plotted as revenue on one axis and order count on a twin axis.Product affinity. Pairs of products bought together, counted with a self-join.
The affinity query was the one I got wrong first. It joins order_items against itself on the same order, and without a condition the pair (A, B) gets counted twice, once as A+B and once as B+A. Adding oi1.product_id < oi2.product_id keeps one canonical direction per pair. One character fixes the entire metric.
The report is generated, not written
The executive summary in reports/executive_summary.md is built from the DataFrames, not from me. Top three categories by revenue, average LTV per segment, the peak revenue month found with idxmax, and the top five affinity pairs. If the data changes, the report changes with it, which is the honest way to write a summary script. Hardcoding the conclusions would have been easier and would have made the whole thing a sticker.
What the pipeline looks like
- Every analysis function returns a DataFrame. The report generator just formats whatever it is handed.
- The SQL and its chart live in the same function, so a broken query is visible immediately instead of failing politely downstream.
- Queries are strings in
analyze.pyfor now. The README documents the extension pattern of moving them intoqueries/files, and the next version of the script will do that, including the NTILE quartile and cohort retention queries the README already shows.
Running it is two commands: pip install pandas matplotlib, then python analyze.py. It writes the charts and the report and prints the tables as it goes.
Top comments (0)