Practice: Revenue and Traffic Queries

This is the classic month-over-month comparison. The trick is that a plain self-join on months is fiddly, so the cleanest version computes the totals per month once and then joins the result to itself with a shifted key.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  order_date TEXT NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, order_date, amount) VALUES
  (1, '2026-01-08', 120.00),
  (2, '2026-01-22', 180.00),
  (3, '2026-02-05', 240.00),
  (4, '2026-02-11',  90.00),
  (5, '2026-02-27', 310.00),
  (6, '2026-03-03', 205.00),
  (7, '2026-03-19', 260.00);

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         ROUND(SUM(amount), 2) AS revenue
  FROM orders
  GROUP BY month
)
SELECT curr.month,
       curr.revenue,
       prev.revenue AS previous_revenue,
       ROUND(curr.revenue - prev.revenue, 2) AS change
FROM monthly AS curr
LEFT JOIN monthly AS prev
       ON prev.month = strftime('%Y-%m', date(prev.month || '-01', '-1 month'))
ORDER BY curr.month;

Three rows come back. January shows 300.00 with no previous month, February shows 640.00 against January's 300.00 for a change of 340.00, and March shows 465.00 against February's 640.00 for a change of -175.00.

The LEFT JOIN is what makes the first row survivable: January has no predecessor, so prev.revenue is NULL and the change is NULL rather than the row being dropped.

Two practical notes. The month-shifting expression is dialect-specific — SQLite uses date(month, '-1 month'), PostgreSQL uses an interval, MySQL uses DATE_SUB. And on a dataset with gaps, a missing month produces no row at all, so a self-join on the month sequence gives a NULL instead of the previous month's value. If gaps matter, generate a calendar table with every month and left join the revenue onto it, which also gives you zero-revenue months rather than missing ones.

Start with the plan, not with a rewrite. Run EXPLAIN or EXPLAIN ANALYZE and look for the two symptoms that dominate slow aggregation: a full table scan where you expected an index seek, and a sort or hash aggregate over far more rows than the query returns.

The first question is how many rows the WHERE clause actually eliminates. If the filter is on a date range, an index on the filtered column lets the engine read only the relevant slice. If the filter wraps the column in a function — WHERE strftime('%Y-%m', order_date) = '2026-02' — the index is useless and you get a full scan. That is the single most common cause of a slow monthly report.

The second question is the grouping key. Grouping by strftime over a column defeats the index as well, so the standard fix is a stored generated column holding the period key, an index on it, and a GROUP BY on that column. That converts a scan-plus-sort into an index range scan with the groups already ordered.

The third question is the join. If the query joins a wide dimension table only to display two columns, a covering index on the join key plus those two columns avoids the lookup entirely. And if the same monthly aggregate is computed on every page load, caching it or materialising it into a summary table is a better answer than optimising the query further.

Finally, be suspicious of the row count in the plan. If the aggregate is processing far more rows than the table has, you are scanning the same data twice because of a join fan-out. That is a correctness smell as well as a performance one, and it usually means a missing DISTINCT or a join that should have been an outer join.

Group by the day, take the maximum, and then join back to get the rows that match it. The two-step form is needed because you cannot reference an aggregate inside another aggregate in the same SELECT list.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  order_date TEXT NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, order_date, amount) VALUES
  (1, '2026-01-08', 120.00),
  (2, '2026-01-08',  80.00),
  (3, '2026-01-22', 180.00),
  (4, '2026-02-05', 240.00),
  (5, '2026-02-05',  90.00),
  (6, '2026-02-27', 310.00);

WITH daily AS (
  SELECT order_date AS sales_day,
         ROUND(SUM(amount), 2) AS day_revenue,
         COUNT(*) AS order_count
  FROM orders
  GROUP BY order_date
)
SELECT sales_day, day_revenue, order_count
FROM daily
WHERE day_revenue = (SELECT MAX(day_revenue) FROM daily)
ORDER BY sales_day;

One row comes back: 2026-02-05, with day_revenue of 330.00 and an order_count of 2. The other days are 2026-01-08 at 200.00 from two orders, 2026-01-22 at 180.00 from one, and 2026-02-27 at 310.00 from one — so the best day is the one where two mid-sized orders happened to fall on the same date, not the day with the single largest order.

The pattern is worth memorising because it recurs. A CTE computes the per-day aggregate, and the outer query filters on a scalar subquery over the same CTE — WHERE day_revenue = (SELECT MAX(day_revenue) FROM daily). Using MAX in a subquery rather than in the outer WHERE avoids the illegal nested-aggregate problem. A window function is the shorter alternative: ROW_NUMBER() OVER (ORDER BY SUM(amount) DESC) = 1 inside the CTE, filtered in the outer query.

One edge case: if two days tie for the maximum, this returns both, because it is an equality test rather than a ranking. That is usually the behaviour you want in a report, but if the requirement is exactly one day, add a tiebreaker to the ORDER BY in the window-function version.