GitHub weekly commit activity trend

Reports the trailing year of weekly commit counts for a repository, smoothed with a four-week moving average. The raw weekly count is noisy - a single large merge distorts it - so the moving average is the column to read for momentum, with the cumulative total giving the period's overall volume.

Query

SELECT
date(week, 'unixepoch') as week_starting,
total as commits,
ROUND(AVG(total) OVER (ORDER BY week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW), 1) as four_week_moving_avg,
SUM(total) OVER (ORDER BY week) as cumulative_commits
FROM github.repos.stats_commit_activity
WHERE owner = '{{owner}}'
AND repo = '{{repo}}';

Variation: day-of-week distribution

The days array holds seven daily counts per week, Sunday first. Exploding it shows which days the project actually ships on:

SELECT
date(week, 'unixepoch') as week_starting,
json_extract(days, '$[0]') as sun,
json_extract(days, '$[1]') as mon,
json_extract(days, '$[2]') as tue,
json_extract(days, '$[3]') as wed,
json_extract(days, '$[4]') as thu,
json_extract(days, '$[5]') as fri,
json_extract(days, '$[6]') as sat
FROM github.repos.stats_commit_activity
WHERE owner = '{{owner}}'
AND repo = '{{repo}}';

Notes

The resource already exposes a per-week total, so there is no need to sum the days array to get a weekly count - and doing so via JSON_EACH is actively worse here, because a predicate sitting in the same WHERE clause as a table function is dropped, which silently returns an empty result rather than an error. week is a Unix timestamp for the Sunday the week begins; convert it with date(week, 'unixepoch'). The window frame ROWS BETWEEN 3 PRECEDING AND CURRENT ROW gives a trailing four-week mean, and the first three rows average over fewer weeks by design. GitHub computes repository statistics asynchronously and returns an empty body with a 202 on the first request for a cold repository, so an empty result may simply mean the statistic is still being generated - retry before concluding the repository is inactive. The series covers the last year only.