Profitability in SQL
Ten thousand sales lines and one question: what sells a lot and earns little.
Data and BI · SQL · Browsable demo
The problem
Turnover is not profit. Almost every business has a product family that fills the window and empties the margin, and another that carries the accounts unnoticed. That does not show in the total: it shows when you cut by product, region and who sells it.
What it does
- Generates a reproducible sales dataset with its own script, so the whole analysis can be re-run.
- Five flat queries over SQLite, with no joins or subqueries: aggregate, group and order.
- Cuts profitability by region, salesperson, category, customer type and month.
- Leaves the output ready for Power BI.
What was decided while building it
- The dashboard reads the queries, not a copy
- The exporter opens the query file itself and runs it against the same dataset the repository publishes. If someone changes a query, the dashboard changes with it. A copy pasted into the script would end up, sooner or later, showing a number the query no longer returns.
- Margin comes from the totals, not an average of averages
- The headline margin is computed over total profit and total revenue, not by averaging each region's margin. Averaging averages gives a different number, equally believable and wrong, and it is the single most repeated mistake in profitability reporting.
- The splitter understands comments
- The last query ends with a comment after the semicolon, and splitting the file on that character turned the comment into a phantom sixth query. A test pins it down, because the failure is invisible: it produces an empty query that breaks nothing and throws the count off.
- The engine is stated: it is SQLite
- The monthly trend query uses a date function specific to SQLite, so these queries are not portable without edits. Claiming «standard SQL» would have sounded better and been false: anyone moving them to another engine has to change that line, and is better off knowing beforehand.
How far it goes
- These are five flat queries: aggregate, group and order. No joins, no subqueries, no window functions. The salesperson «ranking» is a descending order with a limit of five.
- The data is synthetic and, on top of that, every column is drawn independently. Region, category and salesperson bear no relation to the amount: which region is most profitable is sampling noise, not a finding.
- The low-margin query returns no rows at all with this data, because the threshold sits below the average profit the script generates. The context is published so it can be seen, rather than tuning the threshold until something appears.
- The data generator writes to an absolute path from the machine it was written on and does not seed the dates: the published dataset is reproducible, regenerating it as-is is not.
- 10.000 sales lines
Built with
- SQL
- Python
- Power BI
Where the data comes from
Synthetic data generated by the repository itself: no client information involved.