Metric
Revenue
One definition for the whole coffee shop:
quantity * unit_price of the order lines, cancelled orders excluded.SELECT SUM(l.quantity * l.unit_price) AS revenue
FROM orders o
JOIN order_lines l ON l.order_id = o.order_id
WHERE o.status <> 'cancelled'
This is revenue ordered: pending and paid orders count, cancelled ones do not. Cash collected is
another metric, read from payments. Every step that says "revenue" uses this definition. When two reports
disagree, compare their definitions before their SQL. Write a metric once, in one place (a view, a dbt model, a
semantic layer), and reuse it.
SELECT EXTRACT(YEAR FROM o.ordered_at) AS year,
SUM(l.quantity * l.unit_price) AS revenue
FROM orders o
JOIN order_lines l ON l.order_id = o.order_id
WHERE o.status <> 'cancelled'
GROUP BY 1
ORDER BY 1;
| year | revenue |
|---|---|
| 2024 | 81511.00 |
| 2025 | 78934.40 |