Messy CSVsto six reports
A small reporting app built the way a lot of real business reporting starts: messy exports, a cleaning loader, a normalised SQLite database, and raw SQL behind six reports you can filter, chart and export. No ORM, so every query is visible.
Why raw SQLWindow functions, CTEs and query plans are the skills on show. An ORM would hide them.
Messy CSVs to six reports
Crossed-out dots are duplicate customer emails removed by the loader. Orphan orders and missing amounts are kept on purpose, so the data-quality report can show them. Click a stage to read what happens there.
Clean on the way in
The loader standardises region names (trimming, title case, missing becomes Unknown), reads three date formats (YYYY-MM-DD, DD/MM/YYYY, MM/DD/YYYY), and removes duplicate customer emails. It deliberately keeps orphan orders and null amounts, so the data-quality report can show them instead of the loader silently dropping them.
Illustration · sample rows, not the real data
Pipelinemessy CSVs → seed/generate.py → seed/load.py → reports.db → Flask.
Four tables, three joins
A customer has many orders, an order has many line items, and each line item points to one product. Indexes sit on the columns the reports filter and join on.
Indexesidx_orders_date, idx_orders_cust, idx_items_order. The app has an endpoint that shows the EXPLAIN QUERY PLAN for each report.
Six questions, six queries
Pick a report to see what it answers and the SQL idea behind it.
ExportEvery report downloads as CSV or XLSX, with the same filters applied.
Boring in the right places
:named parameter binding. User input never touches the SQL string.pytest -q.Front endJinja templates with vanilla JS and Chart.js. Light on purpose.