Review: Product Engagement Query with Window Functions Written by an AI Assistant

Published 10 Nov, 2026

The growth team wanted a per-product engagement chart, and an AI coding assistant wrote the query behind it. It opened this pull request. You are the reviewer, and nothing merges without your approval.

The assistant's description:

One query for the growth dashboard: daily active users per product over the last 90 days, a 7-day rolling average, week-on-week change and the percentage of active users who bought that product for the first time that day. Window functions keep it to a single pass over the daily rows, and UK days are used throughout.

Where it runs: PostgreSQL 15, session time zone UTC. events has 2 billion rows partitioned by month; occurred_at and orders.created_at are timestamptz. There are about 40 products; the smaller ones often have days with no events at all. Each order is for one product. A checkout with items from two warehouses creates two orders in the same database transaction. The chart is used in the weekly trading meeting to decide which products get marketing budget.

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.

Compare the OVER clauses on lines 35 and 37. With 40 products, what row is 7 rows before a given row on line 37?

A small product has events on 1, 2 and 10 March only. Which days does the "7-day" average for 10 March include?

Two orders created in the same transaction share now(). What does RANK() give each of them?

What type is COUNT(*), and what is 12 / 300 in that type?

The comment says UK days. What time zone does date_trunc('day', ...) use here?

A strong reviewer blocks this change because the week-on-week column compares different products with each other, and the other columns are skewed enough to move budget decisions.

LAG has no partition (line 37, critical). LAG(d.dau, 7) OVER (ORDER BY d.day) runs over all 40 products' rows mixed together, ordered only by day, so the "previous week" value usually belongs to another product, and which one is not even deterministic. Every week-on-week figure is meaningless. Add PARTITION BY d.product_id.
ROWS counts rows, not days (lines 35–36, major). Days with no events produce no row, so for quiet products 6 PRECEDING reaches back weeks and the average is over the wrong days; LAG(..., 7) has the same flaw. Build a calendar with generate_series cross joined to products and fill missing days with 0, or use RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW.
Ties in RANK count a buyer twice (lines 20–21, major). Orders created in the same transaction have identical created_at, so both get rank 1 and the user is counted as two new buyers. Use ROW_NUMBER() with a tie-breaker such as o.id.
Integer division (line 38, major). COUNT returns bigint, so buyers / dau is 0 whenever buyers are fewer than active users, which is almost always. Use 100.0 * COALESCE(nb.buyers, 0) / d.dau.
UTC days, not UK days (line 7, major). With the session in UTC, date_trunc('day', ...) splits days at UTC midnight on lines 7 and 27, so in British Summer Time the first hour of each UK day is counted on the day before. Use date_trunc('day', e.occurred_at AT TIME ZONE 'Europe/London'), or the three-argument date_trunc with the zone.
Partial first day and short first windows (line 10, minor). now() - interval '90 days' starts part-way through a day, so the first day's count is too low, and the first six rolling averages cover fewer than seven days. Start at a UK midnight, read 6 extra days, and return only the last 90.

-- Growth dashboard: daily active users per product, a 7-day rolling
-- average, week-on-week change and the share of active users who
-- bought the product for the first time. Days are UK days.
WITH daily AS (
    SELECT
        e.product_id,
        date_trunc('day', e.occurred_at)     AS day,
        COUNT(DISTINCT e.user_id)            AS dau
    FROM events e
    WHERE e.occurred_at >= now() - interval '90 days'
    GROUP BY 1, 2
),
first_orders AS (
    SELECT product_id, user_id, created_at
    FROM (
        SELECT
            o.product_id,
            o.user_id,
            o.created_at,
            RANK() OVER (PARTITION BY o.product_id, o.user_id
                         ORDER BY o.created_at) AS rn
        FROM orders o
    ) ranked
    WHERE rn = 1
),
new_buyers AS (
    SELECT product_id, date_trunc('day', created_at) AS day, COUNT(*) AS buyers
    FROM first_orders
    GROUP BY 1, 2
)
SELECT
    d.product_id,
    d.day,
    d.dau,
    AVG(d.dau) OVER (PARTITION BY d.product_id ORDER BY d.day
                     ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS dau_7d_avg,
    d.dau - LAG(d.dau, 7) OVER (ORDER BY d.day)                  AS dau_wow_change,
    COALESCE(nb.buyers, 0) / d.dau * 100                         AS new_buyer_pct
FROM daily d
LEFT JOIN new_buyers nb
    ON nb.product_id = d.product_id AND nb.day = d.day
ORDER BY d.product_id, d.day;

{"issues":[
{"id":"lag-partition","file":"analytics/product_engagement.sql","lines":[37,37],"category":"bug","severity":"critical","title":"LAG is not partitioned by product","explanation":"OVER (ORDER BY d.day) puts every product's rows in one sequence, so the row 7 back is usually another product's, in no fixed order. Every week-on-week value is wrong.","keywords":["partition","product","mix","another product","different product","across products","lag"],"fix":"LAG(d.dau, 7) OVER (PARTITION BY d.product_id ORDER BY d.day), ideally over a gap-free calendar."},
{"id":"rows-gaps","file":"analytics/product_engagement.sql","lines":[35,36],"category":"bug","severity":"major","title":"ROWS frame counts rows, not days","explanation":"Days with no events have no row, so for quiet products 6 PRECEDING reaches back more than a week and the 7-day average covers the wrong days. LAG(..., 7) has the same problem.","keywords":["gap","missing day","no row","rows","range","calendar","generate_series","days without","not days","7 rows","interval"],"accept":["edge"],"fix":"Join to a generate_series calendar per product with 0 for missing days, or use RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW."},
{"id":"rank-ties","file":"analytics/product_engagement.sql","lines":[20,21],"category":"bug","severity":"major","title":"RANK ties count a buyer more than once","explanation":"Orders created in the same transaction share created_at, so both rank 1 and the same user is counted as two new buyers.","keywords":["tie","rank","row_number","duplicate","same timestamp","same created_at","twice","double","tiebreak","tie-break"],"accept":["edge"],"fix":"ROW_NUMBER() OVER (PARTITION BY o.product_id, o.user_id ORDER BY o.created_at, o.id)."},
{"id":"intdiv","file":"analytics/product_engagement.sql","lines":[38,38],"category":"bug","severity":"major","title":"Integer division makes the percentage 0","explanation":"Both operands are bigint counts, so buyers / dau truncates to 0 whenever buyers are fewer than active users.","keywords":["integer division","integer","truncat","bigint","100.0","numeric","cast","decimal"],"accept":["edge"],"fix":"100.0 * COALESCE(nb.buyers, 0) / d.dau."},
{"id":"timezone","file":"analytics/product_engagement.sql","lines":[7,7],"category":"bug","severity":"major","title":"date_trunc uses UTC days, not UK days","explanation":"The session time zone is UTC, so days split at UTC midnight on lines 7 and 27. In British Summer Time the first hour of each UK day is counted on the previous day.","keywords":["time zone","timezone","utc","europe/london","at time zone","bst","summer time","london","midnight"],"accept":["edge"],"fix":"date_trunc('day', e.occurred_at AT TIME ZONE 'Europe/London') here and on line 27, or date_trunc('day', ts, 'Europe/London')."},
{"id":"partial-start","file":"analytics/product_engagement.sql","lines":[10,10],"category":"edge","severity":"minor","title":"Partial first day and short first windows","explanation":"now() - interval '90 days' starts mid-day, so the first day is undercounted, and the first six rolling averages cover fewer than seven days.","keywords":["partial","first day","mid-day","midnight","warm","history","boundary","start","fewer than"],"accept":["bug"],"fix":"Start from a UK midnight, read 6 extra days of history, and filter the output to the last 90 days."}
]}

Topics #015 AI Code Review Analytics Medium PostgreSQL SQL Window Functions