The rolling average, with names

Eligibility and compliance thresholds run on trailing averages: hours over the last four weeks, not hours this period. Delivered reporting has no window functions, so practitioners rebuild the lookback by hand, every time it is asked.

RuleCompute lookbacks as window functions over day-grain hours, and publish the partition with the borderline names, not just the counts.

Neverapproximate a trailing average with period totals. The window slides daily; the period snaps monthly; the difference is exactly the people near the line.

◆ The partition, week ending May 29, 2026four-week average against the 30-hour line.

Group	Workers
Full-time schedule, always above	322
Part-time, averaging above 30	9
Part-time, averaging below 30	20
Population, ties to headcount	351

The partition covers the headcount page’s full 351, forced equal by the battery, because a threshold report that silently drops workers is how eligibility gets missed. The nine part-timers averaging above the line are listed by name with their four-week trace, since each is a decision, not a statistic. Sample values are illustrative, never client data.

◆ Why this is hand-built todaywindows slide; delivered reports snap.

A trailing average needs, for each worker and each week, the sum of the prior four weeks of hours: a window function, the same machinery the turnover page uses for denominators. Delivered reporting computes within a row or a group, not across a sliding window, practitioner-reported, so the lookback becomes a monthly export ritual whose result is stale the week after it runs.

◆ The querythe ritual, retired.

-- four-week trailing average hours per worker, weekly
SELECT worker_key, week_end_date,
       AVG(weekly_hours) OVER (
         PARTITION BY worker_key ORDER BY week_end_date
         ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS avg_4wk
FROM (SELECT t.worker_key, d.week_end_date, SUM(t.hours) AS weekly_hours
      FROM fct_time_entry t JOIN dim_date d ON t.work_date_key = d.date_key
      GROUP BY 1, 2)
ORDER BY 1, 2

Use case

Problem

Trailing-hour thresholds are rebuilt manually each cycle, go stale immediately, and quietly drop workers from the population.

What we build

The lookback as a window function over day-grain hours, run weekly, partitioning the full population.

What you get

A standing answer with the borderline names and their traces, instead of a monthly spreadsheet ritual.

Who crossed your 30-hour line this month?

We ship the sliding window that answers weekly, by name.

Talk to us

Terms on this page

trailing window: The last N weeks, sliding forward each week.
window function: Database math across neighboring rows. The lookback’s engine.
partition: Every worker in exactly one group, groups summing to the population.
borderline: Workers near the threshold, listed with their traces.
trace: The weekly hours behind one worker’s average.
eligibility threshold: The average-hours line that triggers a decision.
stale: Computed monthly for a question that moves weekly.