Why should you care?

Board slides for a unicorn need audited SQL metrics, not someone’s one-off Workbench tab from months ago.

See it live — copy this example

Run in MySQL Workbench or the mysql CLI.

WITH daily AS (
  SELECT DATE(placed_at) AS d, SUM(total_inr) AS rev
  FROM orders GROUP BY DATE(placed_at)
)
SELECT d, rev,
       SUM(rev) OVER (ORDER BY d) AS running_rev,
       rev / SUM(rev) OVER () AS share_of_total
FROM daily;

What happened?

• CTE daily aggregates once.
• Outer query adds running total and fraction of all revenue.
• Pattern reusable in BI tools against DataFlow read replica.

Practice next

1. Save query as versioned .sql in git.
2. Run on read replica user (later lesson).
3. Document metric definitions in comment header.
4. Add 7-day moving average with AVG OVER (ROWS 6 PRECEDING).
5. Parameterize date range in prepared statement.

Remember

CTEs clarify multi-step KPIs. Windows add running and share metrics. Treat analytics SQL as production artifact.

DataFlow board pack

Finance runs identical SQL monthly; numbers match audited ledger.

Outcome: CEO trust in dashboard KPIs.