Revenue
Finance emails Harry directly for the first time. Not “filter me these rows” — a real number. “How much revenue did we make last quarter?”
Easy. SELECT SUM(amount) FROM orders. One number, done.
Run it. But before you hit send on whatever comes back — look at the orders table again. Every row in there is money NOVA actually kept?
SELECT SUM(amount) AS total_revenue FROM orders;| total_revenue |
|---|
| 1489.83 |
$1,489.83. Harry’s about to paste that into an email when Hermione points at the status column he skipped right past.
Not every row in orders is a sale. Some got cancelled before they shipped. Some were refunded after the fact. Some are still pending — NOVA hasn’t even collected that money yet. Summing amount across all of them doesn’t answer “how much did we make.” It answers “how much did anyone ever type into an order form,” which is a very different, much less useful number.
Revenue is the money a completed sale actually earned — not every dollar that ever touched an order row.
Split it out by status first, so you can see exactly where the gap comes from:
SELECT status, SUM(amount) AS amount, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY amount DESC;| status | amount | orders |
|---|---|---|
| completed | 1299.85 | 18 |
| pending | 123.98 | 2 |
| cancelled | 39 | 1 |
| refunded | 27 | 1 |
$189.98 of Harry’s original number was orders that never actually turned into revenue. The fix is one WHERE clause:
SELECT SUM(amount) AS total_revenue
FROM orders
WHERE status = 'completed';| total_revenue |
|---|
| 1299.85 |
$1,299.85. That’s the number that goes to Finance. Same table, same column — the only thing that changed is asking which rows actually count.
Finance’s next question is always “okay, but broken down by month.” placed_at is a full date, so grouping by it directly would give you one bucket per day. You need just the year and month.
Here’s the portable way to do that: cast the date to text, then grab the first 7 characters — '2023-05-22' becomes '2023-05'. This works the same whether NOVA is running on SQLite, DuckDB, or Postgres underneath, which is not true of every date trick you’ll see online.
SELECT SUBSTR(CAST(placed_at AS TEXT), 1, 7) AS month,
SUM(amount) AS revenue,
COUNT(*) AS orders
FROM orders
WHERE status = 'completed'
GROUP BY month
ORDER BY month;| month | revenue | orders |
|---|---|---|
| 2022-12 | 199.99 | 1 |
| 2023-01 | 34.5 | 1 |
| 2023-02 | 129.98 | 2 |
| 2023-03 | 274.96 | 3 |
| 2023-04 | 29.99 | 1 |
| 2023-05 | 228.46 | 4 |
| 2023-07 | 64 | 1 |
| 2023-08 | 298.97 | 4 |
| 2023-09 | 39 | 1 |
Notice there’s no row for June 2023 at all — not a row showing $0, just… nothing. NOVA had orders that month, but zero of them completed. A month with zero matching rows doesn’t show up as zero; it just vanishes. Keep that in the back of your mind — it matters more than it sounds like it should, and we’ll come back to exactly why later in this chapter.
Running…
Try it: What happens if you GROUP BY placed_at directly instead of the truncated month?
Run your query to see results here.
You get eighteen rows — almost one per completed order, because placed_at is a specific calendar day. Every day is its own group, so nothing gets aggregated into anything useful. Truncating to 'YYYY-MM' first is what actually buckets orders into months instead of leaving each one alone.
Finance has a follow-up: “Which product category is actually making us money?” Using completed orders only, show each category’s total revenue, joined from products, sorted highest revenue first.
Hint 1
You'll need a JOIN between orders and products — category lives on products, not orders.
Hint 2
Same WHERE status = 'completed' filter as before, then GROUP BY the category column.
Hint 3
SELECT p.category, SUM(o.amount) AS revenue FROM orders o JOIN products p ON p.id = o.product_id WHERE o.status = 'completed' GROUP BY p.category ORDER BY revenue DESC;
Solution
Running…
Every “revenue” question is really two questions stacked together: which rows count, and how do I want them grouped. Get the first one wrong — forget the status filter — and every number built on top of it, by month, by category, by anything, is quietly wrong too.
Revenue means completed transactions, not every row in the orders table — check status before you SUM. And to bucket dates into months portably, cast to text and take the first 7 characters: SUBSTR(CAST(date_col AS TEXT), 1, 7).