Complexity over time queries

These queries map how a repository's size and complexity rose or fell across its rolling labor windows:

  • the series of window totals and its deltas;

  • density and trend;

  • which languages and files drive the change;

  • what changed inside each window.

Read the analysis guide first. Each query here is tested against a seeded schema by TestAnalysisQueriesRun (internal/db).

The data

Table

One row is

Use it for

repo_labor_windows

a window on the default branch: (window_start, window_end]

Totals of the full scc snapshot at end_sha, plus activity counts. Snapshot totals are points in time, so consecutive windows can be compared directly.

repo_labor_rolling

a file in a window's end snapshot

Per-file and per-language complexity at each window end, with Aveloxis's repo_labor columns.

repo_labor_rolling_changed

a file touched by commits that landed in the window

Start-versus-end complexity of touched files, churn, commits and contributors.

The current series

Every query starts from the same series step: the windows of one repository's current default branch, for one window configuration (:window_days, :step_months; 60 and 1 by default), excluding stale windows (needs_recompute).

series = repo_labor_windows
         WHERE repo_id = :repo_id
           AND window_days = :window_days AND step_months = :step_months
           AND NOT needs_recompute
           AND branch_name = the current default branch

Things to keep in mind:

  • Windows overlap. With 60-day windows advanced monthly, each commit lands in about two windows. Snapshot totals (code_complexity, code_lines, ...) are unaffected: each is a point in time. Activity (commits_in_window, repo_labor_rolling_changed) is counted in every window a commit lands in, so never sum it across windows. LAB-10 counts churn from commits directly instead.

  • A delta between consecutive windows spans one step (one month by default). If a window is missing, for example a stale window not yet recomputed, the delta spans the gap. DQ-06 finds gaps.

  • scc versions and count schemes. A series can mix scc versions until labor --recompute remeasures it. A change of scc version can move complexity with no code change. DQ-06 shows the mix.

  • Complexity is scc's. It approximates cyclomatic complexity by counting branch tokens per language. Compare it within a language rather than across languages.

LAB-01 Window series with deltas

Question: How did size and complexity change from one window to the next?

WITH series AS (
    SELECT w.*
    FROM aveloxis_cve.repo_labor_windows w
    WHERE w.repo_id = :repo_id
      AND w.window_days = :window_days AND w.step_months = :step_months
      AND NOT w.needs_recompute
      AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos
                           WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
                           ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
)
SELECT window_end::date        AS window_end,
       left(end_sha, 12)       AS end_sha,
       same_as_previous,
       total_files, code_lines, code_complexity,
       code_complexity - lag(code_complexity) OVER o                                     AS complexity_delta,
       round(100.0 * (code_complexity - lag(code_complexity) OVER o)
             / NULLIF(lag(code_complexity) OVER o, 0), 2)                                AS complexity_pct,
       code_lines - lag(code_lines) OVER o                                               AS code_lines_delta,
       total_files - lag(total_files) OVER o                                             AS files_delta,
       commits_in_window, files_changed, scc_version
FROM series
WINDOW o AS (ORDER BY window_end)
ORDER BY window_end;

Reading the result: same_as_previous true means no first-parent commit landed since the previous window of labor's plan, so the snapshot is the same tree. The deltas are then 0 only when that window is the previous row here (not stale, not missing) and was measured with the same scc_version.

LAB-02 Complexity density

Question: Is the code getting denser (more complexity per line), and how commented is it?

WITH series AS (
    SELECT w.*
    FROM aveloxis_cve.repo_labor_windows w
    WHERE w.repo_id = :repo_id
      AND w.window_days = :window_days AND w.step_months = :step_months
      AND NOT w.needs_recompute
      AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos
                           WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
                           ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
)
SELECT window_end::date AS window_end,
       round(1000.0 * code_complexity / NULLIF(code_lines, 0), 2)              AS complexity_per_kloc,
       round(1.0 * code_complexity / NULLIF(total_files, 0), 2)               AS complexity_per_file,
       round(100.0 * comment_lines / NULLIF(code_lines + comment_lines, 0), 2) AS comment_pct,
       round(1.0 * code_lines / NULLIF(total_files, 0), 1)                    AS code_lines_per_file
FROM series
ORDER BY window_end;

LAB-03 Trend summary

Question: Over the whole series, did complexity go up or down, by how much, how steadily, and at what yearly rate?

WITH series AS (
    SELECT w.*
    FROM aveloxis_cve.repo_labor_windows w
    WHERE w.repo_id = :repo_id
      AND w.window_days = :window_days AND w.step_months = :step_months
      AND NOT w.needs_recompute
      AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos
                           WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
                           ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
), steps AS (
    SELECT window_end, code_complexity, code_lines,
           code_complexity - lag(code_complexity) OVER (ORDER BY window_end) AS d
    FROM series
)
SELECT count(*)                                                    AS windows,
       min(window_end)::date                                       AS first_end,
       max(window_end)::date                                       AS last_end,
       (array_agg(code_complexity ORDER BY window_end))[1]         AS first_complexity,
       (array_agg(code_complexity ORDER BY window_end DESC))[1]    AS last_complexity,
       (array_agg(code_complexity ORDER BY window_end DESC))[1]
         - (array_agg(code_complexity ORDER BY window_end))[1]     AS net_change,
       round(100.0 * ((array_agg(code_complexity ORDER BY window_end DESC))[1]
                      - (array_agg(code_complexity ORDER BY window_end))[1])
             / NULLIF((array_agg(code_complexity ORDER BY window_end))[1], 0), 2) AS net_change_pct,
       count(*) FILTER (WHERE d > 0)                               AS steps_up,
       count(*) FILTER (WHERE d < 0)                               AS steps_down,
       count(*) FILTER (WHERE d = 0)                               AS steps_flat,
       max(d) FILTER (WHERE d > 0)                                 AS largest_rise,
       min(d) FILTER (WHERE d < 0)                                 AS largest_fall,
       round((regr_slope(code_complexity, extract(epoch FROM window_end) / 86400) * 365.25)::numeric, 1) AS trend_per_year,
       round(regr_r2(code_complexity, extract(epoch FROM window_end) / 86400)::numeric, 3)               AS trend_r2
FROM steps;

Reading the result: largest_rise and largest_fall are NULL when no step went that way. trend_per_year is the least-squares slope of complexity against time, in complexity points per year. trend_r2 near 1 means steady growth or decline; near 0 means no linear trend.

LAB-04 Complexity by language per window

Question: Which languages hold the complexity at each window end, and what share?

WITH series AS (
    SELECT w.*
    FROM aveloxis_cve.repo_labor_windows w
    WHERE w.repo_id = :repo_id
      AND w.window_days = :window_days AND w.step_months = :step_months
      AND NOT w.needs_recompute
      AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos
                           WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
                           ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
)
SELECT s.window_end::date AS window_end, r.programming_language,
       count(*)               AS files,
       sum(r.code_lines)      AS code_lines,
       sum(r.code_complexity) AS code_complexity,
       round(100.0 * sum(r.code_complexity)
             / NULLIF(sum(sum(r.code_complexity)) OVER (PARTITION BY s.window_id), 0), 2) AS complexity_share_pct
FROM series s
JOIN aveloxis_cve.repo_labor_rolling r ON r.window_id = s.window_id
GROUP BY s.window_id, s.window_end, r.programming_language
ORDER BY s.window_end, code_complexity DESC, r.programming_language;

Reading the result: a window that counts no files has no rows here; LAB-05 lists it with zeros.

LAB-05 Language change between windows

Question: For each language, how did complexity change from one window to the next, including languages that appear or disappear?

WITH series AS (
    SELECT w.*
    FROM aveloxis_cve.repo_labor_windows w
    WHERE w.repo_id = :repo_id
      AND w.window_days = :window_days AND w.step_months = :step_months
      AND NOT w.needs_recompute
      AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos
                           WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
                           ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
), per_lang AS (
    SELECT r.window_id, r.programming_language,
           sum(r.code_complexity) AS cx, sum(r.code_lines) AS code, count(*) AS files
    FROM aveloxis_cve.repo_labor_rolling r
    WHERE r.window_id IN (SELECT window_id FROM series)
    GROUP BY r.window_id, r.programming_language
), grid AS (
    SELECT s.window_id, s.window_end, l.programming_language
    FROM series s
    CROSS JOIN (SELECT DISTINCT programming_language FROM per_lang) l
)
SELECT g.window_end::date AS window_end, g.programming_language,
       COALESCE(p.files, 0) AS files,
       COALESCE(p.cx, 0)    AS code_complexity,
       COALESCE(p.cx, 0) - lag(COALESCE(p.cx, 0)) OVER o     AS complexity_delta,
       COALESCE(p.code, 0) - lag(COALESCE(p.code, 0)) OVER o AS code_lines_delta
FROM grid g
LEFT JOIN per_lang p ON p.window_id = g.window_id AND p.programming_language = g.programming_language
WINDOW o AS (PARTITION BY g.programming_language ORDER BY g.window_end)
ORDER BY g.programming_language, g.window_end;

LAB-06 Files with the largest change over the series

Question: Between the first and the latest window of the series, which files gained or lost the most complexity, including files added or removed?

WITH series AS (
    SELECT w.*
    FROM aveloxis_cve.repo_labor_windows w
    WHERE w.repo_id = :repo_id
      AND w.window_days = :window_days AND w.step_months = :step_months
      AND NOT w.needs_recompute
      AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos
                           WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
                           ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
), ends AS (
    SELECT (SELECT window_id FROM series ORDER BY window_end LIMIT 1)      AS first_id,
           (SELECT window_id FROM series ORDER BY window_end DESC LIMIT 1) AS last_id
), first_snap AS (
    SELECT r.* FROM aveloxis_cve.repo_labor_rolling r, ends WHERE r.window_id = ends.first_id
), last_snap AS (
    SELECT r.* FROM aveloxis_cve.repo_labor_rolling r, ends WHERE r.window_id = ends.last_id
)
SELECT COALESCE(l.file_path, f.file_path)                         AS file_path,
       COALESCE(l.programming_language, f.programming_language)   AS programming_language,
       CASE WHEN f.file_path IS NULL THEN 'added'
            WHEN l.file_path IS NULL THEN 'removed'
            WHEN f.blob_sha = l.blob_sha THEN 'unchanged'
            ELSE 'modified' END                                   AS status,
       f.code_complexity AS first_complexity,
       l.code_complexity AS last_complexity,
       COALESCE(l.code_complexity, 0) - COALESCE(f.code_complexity, 0) AS complexity_delta,
       COALESCE(l.code_lines, 0) - COALESCE(f.code_lines, 0)           AS code_lines_delta
FROM first_snap f
FULL JOIN last_snap l ON l.file_path = f.file_path
ORDER BY abs(COALESCE(l.code_complexity, 0) - COALESCE(f.code_complexity, 0)) DESC, file_path
LIMIT 50;

Reading the result: a renamed file shows as one removed and one added row, since snapshots are compared by path. Snapshots hold only the files scc counted, so a file the first window's scc did not count shows as added (and the reverse as removed).

LAB-07 History of one file

Question: How did one file's complexity evolve, window by window?

WITH series AS (
    SELECT w.*
    FROM aveloxis_cve.repo_labor_windows w
    WHERE w.repo_id = :repo_id
      AND w.window_days = :window_days AND w.step_months = :step_months
      AND NOT w.needs_recompute
      AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos
                           WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
                           ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
)
SELECT s.window_end::date AS window_end,
       r.blob_sha, r.programming_language, r.code_lines, r.code_complexity,
       r.code_complexity - lag(r.code_complexity) OVER o   AS complexity_delta,
       CASE WHEN row_number() OVER o > 1 AND (r.blob_sha IS NOT NULL OR lag(r.blob_sha) OVER o IS NOT NULL)
            THEN r.blob_sha IS DISTINCT FROM lag(r.blob_sha) OVER o END AS content_changed,
       c.change_type AS change_in_window, c.commits AS commits_in_window,
       c.lines_added, c.lines_removed
FROM series s
LEFT JOIN aveloxis_cve.repo_labor_rolling r
       ON r.window_id = s.window_id AND r.file_path = :'file_path'
LEFT JOIN aveloxis_cve.repo_labor_rolling_changed c
       ON c.window_id = s.window_id AND c.file_path = :'file_path'
WINDOW o AS (ORDER BY s.window_end)
ORDER BY s.window_end;

Reading the result: a NULL code_complexity means the file was absent at that window end, skipped by the scc walk, or not counted by scc. content_changed compares with the previous window end, so it is NULL for the first, and NULL when the snapshot has no row for the file at either end: the snapshot cannot tell whether it changed. With a row at one end only, t can also mean only that one end counted the file and the other did not (a different scc version, say); change_in_window says whether commits touched it.

LAB-08 What changed inside each window

Question: In each window, how many touched files were added, deleted or modified, how much did their complexity move, and how much churn was there?

WITH series AS (
    SELECT w.*
    FROM aveloxis_cve.repo_labor_windows w
    WHERE w.repo_id = :repo_id
      AND w.window_days = :window_days AND w.step_months = :step_months
      AND NOT w.needs_recompute
      AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos
                           WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
                           ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
)
SELECT s.window_start::date AS window_start, s.window_end::date AS window_end,
       s.commits_in_window,
       count(c.file_path)                                 AS touched_files,
       count(*) FILTER (WHERE c.change_type = 'A')        AS added,
       count(*) FILTER (WHERE c.change_type = 'D')        AS deleted,
       count(*) FILTER (WHERE c.change_type = 'M')        AS modified,
       count(*) FILTER (WHERE c.change_type = 'N')        AS touched_net_unchanged,
       count(*) FILTER (WHERE c.change_type = 'X')        AS created_and_removed,
       count(c.delta_code_complexity)                     AS measured_files,
       COALESCE(sum(c.delta_code_complexity), 0)         AS complexity_delta_touched,
       COALESCE(sum(c.delta_code_complexity) FILTER (WHERE c.delta_code_complexity > 0), 0) AS complexity_added,
       COALESCE(sum(c.delta_code_complexity) FILTER (WHERE c.delta_code_complexity < 0), 0) AS complexity_removed,
       COALESCE(sum(c.lines_added), 0)                    AS lines_added,
       COALESCE(sum(c.lines_removed), 0)                  AS lines_removed
FROM series s
LEFT JOIN aveloxis_cve.repo_labor_rolling_changed c ON c.window_id = s.window_id
GROUP BY s.window_id, s.window_start, s.window_end, s.commits_in_window
ORDER BY s.window_end;

Reading the result:

  • complexity_delta_touched compares the touched files at the window's start and end snapshots. It is the window's own change, while LAB-01 compares one window end with the previous end.

  • Files whose metrics are NULL on one side (walk-skipped or not counted) are not in measured_files or the sums.

LAB-09 Biggest complexity movers in each window

Question: In each window, which touched files added or removed the most complexity?

WITH series AS (
    SELECT w.*
    FROM aveloxis_cve.repo_labor_windows w
    WHERE w.repo_id = :repo_id
      AND w.window_days = :window_days AND w.step_months = :step_months
      AND NOT w.needs_recompute
      AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos
                           WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
                           ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
), ranked AS (
    SELECT s.window_end, c.file_path, c.change_type, c.programming_language,
           c.start_code_complexity, c.end_code_complexity, c.delta_code_complexity,
           c.commits, c.distinct_contributors,
           row_number() OVER (PARTITION BY s.window_id ORDER BY c.delta_code_complexity DESC, c.file_path) AS up_rank,
           row_number() OVER (PARTITION BY s.window_id ORDER BY c.delta_code_complexity ASC, c.file_path)  AS down_rank
    FROM series s
    JOIN aveloxis_cve.repo_labor_rolling_changed c ON c.window_id = s.window_id
    WHERE c.delta_code_complexity IS NOT NULL AND c.delta_code_complexity <> 0
)
SELECT window_end::date AS window_end,
       CASE WHEN delta_code_complexity > 0 THEN 'rise' ELSE 'fall' END AS direction,
       file_path, change_type, programming_language,
       start_code_complexity, end_code_complexity, delta_code_complexity,
       commits, distinct_contributors
FROM ranked
WHERE (delta_code_complexity > 0 AND up_rank <= 5) OR (delta_code_complexity < 0 AND down_rank <= 5)
ORDER BY window_end, direction DESC, abs(delta_code_complexity) DESC, file_path;

LAB-10 Churn hotspots on the default branch

Question: Which files changed most often on the default branch over the series' span, and how complex are they now?

The counts come from the commits themselves, not from the overlapping windows, so each commit counts once.

WITH series AS (
    SELECT w.*
    FROM aveloxis_cve.repo_labor_windows w
    WHERE w.repo_id = :repo_id
      AND w.window_days = :window_days AND w.step_months = :step_months
      AND NOT w.needs_recompute
      AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos
                           WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
                           ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
), span AS (
    SELECT min(window_start) AS from_ts, max(window_end) AS to_ts,
           (SELECT window_id FROM series ORDER BY window_end DESC LIMIT 1) AS last_id
    FROM series
), churn AS (
    SELECT cf.file_path,
           count(DISTINCT bc.commit_id)                                         AS commits,
           count(DISTINCT COALESCE(c.cntrb_id::text, lower(c.author_email)))    AS contributors,
           COALESCE(sum(cf.lines_added), 0)                                     AS lines_added,
           COALESCE(sum(cf.lines_removed), 0)                                   AS lines_removed
    FROM aveloxis_cve.branch_commits bc
    JOIN aveloxis_cve.commits c ON c.commit_id = bc.commit_id
    JOIN aveloxis_cve.commit_files cf ON cf.commit_id = bc.commit_id
    CROSS JOIN span
    WHERE bc.branch_id = (SELECT branch_id FROM aveloxis_cve.branch_repos
                          WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
                          ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
      AND bc.landed_at >= span.from_ts AND bc.landed_at <= span.to_ts
    GROUP BY cf.file_path
)
SELECT ch.file_path, ch.commits, ch.contributors, ch.lines_added, ch.lines_removed,
       r.programming_language, r.code_lines AS code_lines_now, r.code_complexity AS complexity_now
FROM churn ch
CROSS JOIN span
LEFT JOIN aveloxis_cve.repo_labor_rolling r ON r.window_id = span.last_id AND r.file_path = ch.file_path
ORDER BY ch.commits DESC, ch.lines_added + ch.lines_removed DESC, ch.file_path
LIMIT 50;

Reading the result: files with high churn and high current complexity are the usual candidates for maintenance risk. complexity_now is NULL for a file no longer present, skipped by the walk, or not counted.

LAB-11 Activity against complexity change

Question: Do busier windows (more commits, more contributors) see larger complexity changes?

WITH series AS (
    SELECT w.*
    FROM aveloxis_cve.repo_labor_windows w
    WHERE w.repo_id = :repo_id
      AND w.window_days = :window_days AND w.step_months = :step_months
      AND NOT w.needs_recompute
      AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos
                           WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
                           ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
), activity AS (
    SELECT s.window_id, s.window_end, s.commits_in_window, s.code_complexity,
           (SELECT count(DISTINCT COALESCE(c.cntrb_id::text, lower(c.author_email)))
            FROM aveloxis_cve.branch_commits bc
            JOIN aveloxis_cve.commits c ON c.commit_id = bc.commit_id
            WHERE bc.branch_id = (SELECT branch_id FROM aveloxis_cve.branch_repos
                                  WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
                                  ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
              AND bc.landed_at <= s.window_end
              AND (bc.landed_at > s.window_start
                   OR (s.window_end = (SELECT min(f.window_end) FROM aveloxis_cve.repo_labor_windows f
                                       WHERE f.repo_id = s.repo_id AND f.branch_name = s.branch_name
                                         AND f.window_days = s.window_days AND f.step_months = s.step_months)
                       AND bc.landed_at = s.window_start))
           ) AS contributors
    FROM series s
), steps AS (
    SELECT window_end, commits_in_window, contributors,
           code_complexity - lag(code_complexity) OVER (ORDER BY window_end) AS complexity_delta
    FROM activity
)
SELECT window_end::date AS window_end, commits_in_window, contributors, complexity_delta,
       round(corr(commits_in_window, abs(complexity_delta)) OVER ()::numeric, 3) AS corr_commits_abs_delta,
       round(corr(contributors, abs(complexity_delta)) OVER ()::numeric, 3)      AS corr_contributors_abs_delta
FROM steps
ORDER BY window_end;

Reading the result:

  • Contributors are counted as labor counts them: by resolved Aveloxis contributor, else by lowercased author email.

  • The first window of the configuration is the closed interval [start, end], as in labor. It is found among all the configuration's windows, stale ones included, so a stale first window does not move the rule to the second.

  • The correlation columns repeat on every row. The first window has no delta, so they need three windows: with fewer they are NULL, and with exactly three they are +1 or -1 by construction and mean nothing. They are also NULL when a column does not vary.

  • Window activity and the end-to-end delta cover different spans: activity covers the 60-day window, the delta one step. Read the correlation as a rough signal only.

LAB-12 Where each step's change came from

Question: For each step between consecutive window ends, how much of the complexity change came from files added, removed or modified, and from files re-measured by a different scc version?

WITH series AS (
    SELECT w.*
    FROM aveloxis_cve.repo_labor_windows w
    WHERE w.repo_id = :repo_id
      AND w.window_days = :window_days AND w.step_months = :step_months
      AND NOT w.needs_recompute
      AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos
                           WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
                           ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
), pairs AS (
    SELECT window_id AS cur_id, window_end,
           lag(window_id) OVER (ORDER BY window_end) AS prev_id
    FROM series
), diff AS (
    SELECT p.window_end,
           CASE WHEN d.prev_path IS NULL AND d.cur_path IS NULL THEN NULL  -- a step between two windows with no files
                WHEN d.prev_path IS NULL THEN 'added'
                WHEN d.cur_path IS NULL THEN 'removed'
                WHEN d.prev_blob <> d.cur_blob THEN 'modified'
                WHEN d.prev_cx IS DISTINCT FROM d.cur_cx THEN 'remeasured'
                ELSE 'unchanged' END AS kind,
           COALESCE(d.cur_cx, 0) - COALESCE(d.prev_cx, 0)     AS d_cx,
           COALESCE(d.cur_code, 0) - COALESCE(d.prev_code, 0) AS d_code
    FROM pairs p
    LEFT JOIN LATERAL (
        SELECT cr.file_path AS cur_path, pr.file_path AS prev_path,
               cr.blob_sha AS cur_blob, pr.blob_sha AS prev_blob,
               cr.code_complexity AS cur_cx, pr.code_complexity AS prev_cx,
               cr.code_lines AS cur_code, pr.code_lines AS prev_code
        FROM (SELECT * FROM aveloxis_cve.repo_labor_rolling WHERE window_id = p.cur_id) cr
        FULL JOIN (SELECT * FROM aveloxis_cve.repo_labor_rolling WHERE window_id = p.prev_id) pr
               ON pr.file_path = cr.file_path
    ) d ON TRUE
    WHERE p.prev_id IS NOT NULL
)
SELECT window_end::date AS window_end,
       sum(d_cx)                                       AS complexity_delta,
       sum(d_cx) FILTER (WHERE kind = 'added')         AS from_added_files,
       sum(d_cx) FILTER (WHERE kind = 'removed')       AS from_removed_files,
       sum(d_cx) FILTER (WHERE kind = 'modified')      AS from_modified_files,
       sum(d_cx) FILTER (WHERE kind = 'remeasured')    AS from_remeasured_files,
       count(*) FILTER (WHERE kind = 'added')          AS files_added,
       count(*) FILTER (WHERE kind = 'removed')        AS files_removed,
       count(*) FILTER (WHERE kind = 'modified')       AS files_modified,
       sum(d_code)                                     AS code_lines_delta
FROM diff
GROUP BY window_end
ORDER BY window_end;

Reading the result:

  • complexity_delta equals the step's delta in LAB-01, and the from_* columns add up to it (NULL where nothing of that kind happened).

  • from_remeasured_files is non-zero only when the two windows were measured with different scc versions: the same content scored differently.

  • A rename counts as one removed and one added file.

  • Snapshots hold only the files scc counted. A file the previous window's scc did not count shows as added, and one the current window's scc does not count as removed, even if it was present throughout. DQ-06 shows a series measured with more than one scc version.

  • A step between two windows that count no files is still listed, with deltas of 0.

LAB-13 Complexity without generated and vendored files

Question: How much of each window's complexity is in generated or vendored files, and how did the hand-written code's complexity change?

Generated code (parsers, protocol stubs, converter loaders, bundled JavaScript) can dominate a repository's complexity and its growth. This query splits each snapshot by path.

WITH series AS (
    SELECT w.*
    FROM aveloxis_cve.repo_labor_windows w
    WHERE w.repo_id = :repo_id
      AND w.window_days = :window_days AND w.step_months = :step_months
      AND NOT w.needs_recompute
      AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos
                           WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
                           ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
), generated (re) AS (
    -- Paths treated as generated or vendored. Heuristics: adjust them to the
    -- project. Build-output directories count only at the repository root,
    -- since a package or module of that name elsewhere is usually source.
    -- src/gen/, gen-*/ and gen_*/ hold Thrift output (Hive, HBase, Accumulo). Hive's
    -- ql/src/gen/vectorization/ templates are hand-written text that scc
    -- scores 0, so counting them generated changes no complexity.
    VALUES ('^(target|build|out|dist|gen|deps)/'),
           ('(^|/)(generated|generated-sources|generated-test-sources|vendor|third[_-]?party|node_modules|bower_components)/'),
           ('(^|/)src/gen/'), ('(^|/)([a-z]+-)?gen[-_](java|javabean|cpp|py|php|rb|go|js|nodejs|perl|netstd|csharp|erl|lua)/'),
           ('(^|/)\.pnp\.c?js$'), ('\.min\.(js|css)$'), ('(\.pb|_pb2|_pb2_grpc)\.(go|py)$'), ('\.pb\.(cc|h|gw\.go)$'),
           ('(^|/)zz_generated[^/]*$'), ('_generated\.[a-z]+$'), ('\.generated\.')
), flags AS MATERIALIZED (
    -- Each distinct path is matched once: paths repeat across windows.
    SELECT p.file_path, EXISTS (SELECT 1 FROM generated g WHERE p.file_path ~* g.re) AS is_generated
    FROM (SELECT DISTINCT r.file_path
          FROM aveloxis_cve.repo_labor_rolling r
          WHERE r.window_id IN (SELECT window_id FROM series)) p
), split AS (
    -- A window that counts no files keeps one row of NULLs, so it still
    -- appears and handwritten_delta compares adjacent windows.
    SELECT s.window_id, s.window_end, f.is_generated, r.code_complexity, r.code_lines
    FROM series s
    LEFT JOIN aveloxis_cve.repo_labor_rolling r ON r.window_id = s.window_id
    LEFT JOIN flags f ON f.file_path = r.file_path
), per_window AS (
    SELECT window_end,
           COALESCE(sum(code_complexity), 0)                     AS total_complexity,
           COALESCE(sum(code_complexity) FILTER (WHERE is_generated), 0) AS generated_complexity,
           COALESCE(sum(code_complexity) FILTER (WHERE NOT is_generated), 0) AS handwritten_complexity,
           COALESCE(sum(code_lines) FILTER (WHERE NOT is_generated), 0)      AS handwritten_code_lines,
           count(*) FILTER (WHERE is_generated)                  AS generated_files
    FROM split
    GROUP BY window_id, window_end
)
SELECT window_end::date AS window_end, total_complexity, generated_complexity, generated_files,
       round(100.0 * generated_complexity / NULLIF(total_complexity, 0), 2)              AS generated_pct,
       handwritten_complexity,
       handwritten_complexity - lag(handwritten_complexity) OVER (ORDER BY window_end)  AS handwritten_delta,
       round(1000.0 * handwritten_complexity / NULLIF(handwritten_code_lines, 0), 2)     AS handwritten_per_kloc
FROM per_window
ORDER BY window_end;

Reading the result:

  • handwritten_complexity is the series to read for how the code people write changes. generated_pct says how much the raw totals overstate it.

  • Check the patterns with LAB-14, which lists the files they match and miss.

  • A window that counts no files is listed with totals of 0 and NULL ratios.

LAB-14 Most complex files at the latest window end

Question: Which files carry the most complexity now, and which of them are generated or vendored?

WITH series AS (
    SELECT w.*
    FROM aveloxis_cve.repo_labor_windows w
    WHERE w.repo_id = :repo_id
      AND w.window_days = :window_days AND w.step_months = :step_months
      AND NOT w.needs_recompute
      AND w.branch_name = (SELECT branch_name FROM aveloxis_cve.branch_repos
                           WHERE repo_id = :repo_id AND is_default AND archive_reason IS NULL
                           ORDER BY last_seen_at DESC, branch_id DESC LIMIT 1)
), generated (re) AS (
    -- Paths treated as generated or vendored. Heuristics: adjust them to the
    -- project. Build-output directories count only at the repository root,
    -- since a package or module of that name elsewhere is usually source.
    -- src/gen/, gen-*/ and gen_*/ hold Thrift output (Hive, HBase, Accumulo). Hive's
    -- ql/src/gen/vectorization/ templates are hand-written text that scc
    -- scores 0, so counting them generated changes no complexity.
    VALUES ('^(target|build|out|dist|gen|deps)/'),
           ('(^|/)(generated|generated-sources|generated-test-sources|vendor|third[_-]?party|node_modules|bower_components)/'),
           ('(^|/)src/gen/'), ('(^|/)([a-z]+-)?gen[-_](java|javabean|cpp|py|php|rb|go|js|nodejs|perl|netstd|csharp|erl|lua)/'),
           ('(^|/)\.pnp\.c?js$'), ('\.min\.(js|css)$'), ('(\.pb|_pb2|_pb2_grpc)\.(go|py)$'), ('\.pb\.(cc|h|gw\.go)$'),
           ('(^|/)zz_generated[^/]*$'), ('_generated\.[a-z]+$'), ('\.generated\.')
), latest AS (
    SELECT window_id FROM series ORDER BY window_end DESC LIMIT 1
)
SELECT r.file_path, r.programming_language, r.code_lines, r.code_complexity,
       round(100.0 * r.code_complexity / NULLIF(sum(r.code_complexity) OVER (), 0), 2) AS pct_of_total,
       EXISTS (SELECT 1 FROM generated g WHERE r.file_path ~* g.re)                  AS is_generated
FROM aveloxis_cve.repo_labor_rolling r
WHERE r.window_id = (SELECT window_id FROM latest)
ORDER BY r.code_complexity DESC, r.file_path
LIMIT 50;

Reading the result: check both directions. A large file with is_generated false that is in fact generated (a checked-in parser, a schema binding) needs a pattern added to the generated step of this query and LAB-13. A hand-written file with is_generated true needs its pattern narrowed: a project whose real source lives under a root build/ or gen/ directory, for example.