Review: Daily Revenue Report Query Written by an AI Assistant

The finance team asked for a daily revenue report by acquisition channel, and an AI coding assistant wrote the query behind the new dashboard tile. It opened this pull request. You are the reviewer, and nothing merges without your approval.

The assistant's description:

Adds the daily revenue query for the finance dashboard. Gross revenue comes from line items, refunds are netted off, and a refund rate is included. Uses COUNT(DISTINCT) so orders are never double counted. Staff orders are excluded as requested. Indexed on created_at, so it is fast.

Where it runs: PostgreSQL 15 read replica, session time zone UTC. orders has 50 million rows with a B-tree index on created_at (a timestamptz). Orders average 3 line items. Partial refunds are common, so one order can have several rows in refunds, each with a status of pending, approved or rejected. coupon_code is NULL for about 90% of orders. Finance closes the books on UK calendar days (Europe/London). The tile refreshes every 15 minutes and must finish in under 10 seconds for a 31-day range.

Your task

• Flag every problem you would block the merge for. Click a line number to comment, and Shift-click to cover a range.
• For each one, choose the issue type and severity, and explain what fails and how to fix it.
• You are scored on the problems you catch, the right issue type and a clear explanation. Missing a critical problem fails the round.

Aim for 15 minutes, as in a real review round.

Take one order with 3 line items and 2 refunds. How many rows does it have after the joins, and what do the two SUMs add up?

An order with no refund at all: what is r.status for it, and does it pass the WHERE clause?

What is NULL <> 'STAFF100'? True, false, or something else?

What type do the two COUNTs return, and what is 4 / 50 in that type?

An order placed at 00:30 in London on 1 July: which day does it land on, and can the planner use the index for created_at::date?

A strong reviewer blocks this change because almost every number it produces is wrong: the joins multiply both revenue and refunds, and the refund filter silently removes every order that was not refunded. COUNT(DISTINCT o.id) hides the fan-out for the order count only, which makes the report look plausible.

1. Fan-out double counting (lines 15–16, critical). Joining line items and refunds to the same order produces items × refunds rows. An order with 3 items and 2 refunds becomes 6 rows, so each item's revenue is summed twice and each refund three times. Aggregate each child table in its own subquery (revenue per order, refunds per order) and join the totals, one row per order.
2. The refund filter turns the LEFT JOIN into an inner join (line 19, critical). For orders without a refund, r.status is NULL, so r.status = 'approved' discards them. The report then shows only refunded orders. Move the condition into the join (ON r.order_id = o.id AND r.status = 'approved') or into the refunds subquery.
3. NULL coupon codes are excluded (line 18, major). NULL <> 'STAFF100' is NULL, not true, so the 90% of orders with no coupon disappear. Use o.coupon_code IS DISTINCT FROM 'STAFF100'.
4. Integer division (line 12, major). Both counts are bigint, so 4 / 50 is 0 and the refund rate is almost always 0 or 100. Multiply first in numeric: 100.0 * COUNT(DISTINCT r.order_id) / NULLIF(COUNT(DISTINCT o.id), 0).
5. UTC days, not UK days (line 5, major). With the session in UTC, date_trunc('day', created_at) and the filter use UTC midnight, so during British Summer Time orders between 00:00 and 01:00 London time land on the previous day and month-end totals do not match the books. Convert first: date_trunc('day', o.created_at AT TIME ZONE 'Europe/London'), and build the range bounds the same way.
6. Non-sargable date filter (line 17, major). Casting the column, o.created_at::date, stops the planner from using the index on created_at, so every refresh scans 50 million rows. Compare the raw column with a half-open range: o.created_at >= :start AND o.created_at < :end + 1 day, with the bounds computed in Europe/London.