This is where an analytical engine stops being a novelty. Two queries here produce every number the dashboard course will draw: totals per item, and a per-day series carrying a seven-day trailing average. The second one contains an expression that reads like a mistake, and understanding why it is not is the most portable thing in this course. Checkpoint: engine-07.

The ordinary aggregate first

Totals per item is a plain GROUP BY, and it is worth writing down as the baseline the next query departs from:

const rows = await runPrepared(
    `SELECT item, SUM(amount) AS total FROM sales WHERE ${filterClause(fruits)}
     GROUP BY item ORDER BY item`,
    [startISO, endISO, ...fruits],
);
return rows.map((r): ChartDatum => ({ item: String(r.item), amount: Number(r.total) }));

Eight input items, eight output rows, twenty thousand rows collapsed on the way. ORDER BY item is deliberate — a stable alphabetical order means a bar chart does not reshuffle itself every time a filter changes, which is a rendering decision being made correctly in SQL.

Four numbers a day, one pass

Now the per-day series. Four things per day: how many rows, what they totalled, how many distinct items appeared, and the seven-day trailing average of the total.

SELECT date, COUNT(*) AS rows, SUM(amount) AS total, COUNT(DISTINCT item) AS items,
       AVG(SUM(amount)) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)::DOUBLE AS ma7
FROM sales WHERE date BETWEEN CAST(? AS DATE) AND CAST(? AS DATE)
GROUP BY date ORDER BY date

AVG(SUM(amount)) is the line that stops people. An average of a sum reads like someone nested the wrong two functions.

It is exactly right, because the two run at different times. GROUP BY date collapses each day into one row, and SUM(amount) is what that collapse produces — one number per day. Only after grouping does the window run, and it sees a sequence of daily sums, one per row. AVG averages those.

The frame says which of them:

OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

Order the grouped rows by date; for each one, take the six before it and itself. Seven days, sliding, recomputed per row by the engine in a single pass over already-grouped data.

The alternative — fetch daily totals, loop in JavaScript, maintain a rolling window — is perhaps fifteen lines, has an off-by-one in the first draft, and ships the whole series across a worker boundary to do arithmetic the engine finished before it replied. This is the shape of the whole argument for putting a real engine in the browser: not “SQL is nicer”, but that the work happens where the data already is.

::DOUBLE is a DuckDB cast, and it is load-bearing. Without it the average comes back as a decimal type that arrives in JavaScript as an object rather than a number, and Number() on it gives you NaN at the far end of a worker boundary with nothing useful in the stack trace.

The edge the frame creates

Look at the first six rows of output and the averages climb steadily before settling. That is not a bug and it is worth predicting rather than discovering.

6 PRECEDING means up to six preceding rows. On day one there are none, so the average is that single day. On day two it averages two days. Only from day seven is it averaging a full week. Every trailing-average chart has this ramp; most dashboards either ignore it, or hide the first n-1 points, and knowing which you chose is the difference between a considered decision and an artifact.

Day seven is also your verification. The seventh row’s ma7 must equal the mean of the first seven total values, and you can check that with a calculator against the numbers on your own screen. When it matches, the window frame is right — not “looks plausible”, but arithmetically confirmed.

Two typed results

Both queries get an interface, and DailyDatum documents the thing that is not obvious:

export interface ChartDatum {
    item: string;
    amount: number;
}

/** One day's aggregates for the time series, sparklines, and heatmap. */
export interface DailyDatum {
    date: string;
    rows: number;
    total: number;
    items: number;
    /** 7-day trailing moving average of total (SQL window function). */
    ma7: number;
}

One query feeds three visualisations in the dashboard course — a line chart, the sparklines behind the stat cards, and a calendar heatmap. That is the payoff of computing everything per day in one statement: the components that come later are all reading different columns of the same result.

Build it

File Action What goes in it
src/types.ts modify ChartDatum and DailyDatum; add aggregateForChart and aggregateByDate to DbWorkerApi
src/db-worker.ts modify Both aggregate functions, the window expression with its ::DOUBLE cast; expose them
index.html modify A totals table and a daily table with five columns, plus a note element
src/index.ts modify Render both; show the first ten days and the number of days in range
src/style.css modify A note style for the caption under the daily table

Done when: the totals table lists eight items, the daily table reports 180 days in range, and the seventh day’s ma7 equals the mean of the first seven total values when you check it by hand.

Answer key: engine-06...engine-07.

Challenge: widen the window, then break it

Change 6 PRECEDING to 29 PRECEDING and watch the series flatten — a thirty-day average of noisy daily data is nearly a straight line, which is a useful thing to have seen when someone asks for “smoother”. Then swap ROWS for RANGE and read DuckDB’s response carefully. ROWS counts rows; RANGE works in values of the ordering column, and the difference between them is exactly what happens when a day has no sales at all.

Next lesson: a query that has to know its own column names before it can run.