WITH actor_days AS (
      SELECT
        actor_login,
        uniqMerge(repos) AS repos,
        sum(pushes) AS pushes,
        sum(commits) AS commits,
        sum(prs_opened) AS prs,
        sum(prs_merged) AS mergedPrs
      FROM gh_actor_daily
      -- Anchored to the rollup's own high-water day, not wall clock, so the
      -- window self-heals after ingestion lag - same reasoning as the
      -- max(created_at) anchor this replaces (CLAUDE.md gotcha notes).
      WHERE day > (SELECT max(day) FROM gh_actor_daily) - {days: UInt32}
        AND actor_login != ''
      GROUP BY actor_login
    ),
    per_actor AS (
      SELECT
        actor_login AS actor,
        actor_login ILIKE '%[bot]%' AS is_bot,
        pushes,
        repos,
        commits,
        prs,
        mergedPrs
      FROM actor_days
    ),
    meta AS (
      SELECT
        countIf(is_bot) AS bot_count,
        countIf(NOT is_bot AND repos = 1 AND pushes >= {megaPushThreshold: UInt32}) AS mega_pusher_count,
        countIf(NOT is_bot AND NOT (repos = 1 AND pushes >= {megaPushThreshold: UInt32})) AS kept_count
      FROM per_actor
    ),
    -- Issue #40: GitHub-REST-enriched merged-PR counts (gh_actor_pr_stats,
    -- migration 20260721000012), fed by the refreshActorPrStats Trigger.dev job.
    -- ReplacingMergeTree(fetched_at) ORDER BY actor_login, so FINAL already
    -- collapses to one row per actor. merged_prs_7d is the count merged within
    -- THIS scatter's window, so dividing it by the window's p.prs (below) yields
    -- a bounded merge rate instead of a lifetime-count-over-window blowup.
    enriched AS (
      SELECT actor_login, merged_prs_7d AS merged_prs, 1 AS has_stats
      FROM gh_actor_pr_stats FINAL
    )
    SELECT
      p.actor AS actor,
      p.pushes AS pushes,
      p.repos AS repos,
      p.commits AS commits,
      p.prs AS prs,
      -- BEGIN issue #40 JOIN/ranking change (reconcile with issue #41 if it
      -- also touches this SELECT/ORDER BY): prefer the enriched merged-PR
      -- count over the firehose-derived sum(pr_merged), which is push-dominated
      -- and sparse (CLAUDE.md gotcha #4). en.has_stats = 1 is how we detect a
      -- real join match, since ClickHouse defaults unmatched LEFT JOIN columns
      -- to 0/empty rather than NULL.
      if(en.has_stats = 1, en.merged_prs, p.mergedPrs) AS mergedPrs,
      m.bot_count AS bot_count,
      m.mega_pusher_count AS mega_pusher_count,
      m.kept_count AS kept_count
    FROM per_actor AS p
    CROSS JOIN meta AS m
    LEFT JOIN enriched AS en ON en.actor_login = p.actor
    WHERE NOT p.is_bot
      AND NOT (p.repos = 1 AND p.pushes >= {megaPushThreshold: UInt32})
    -- Rank by merged-PR signal, real GitHub-REST data first. Zero-PR actors
    -- would score a spurious 1.0 from (mergedPrs+1)/(prs+1), so gate them below
    -- anyone with real PR activity rather than letting push-only accounts tie
    -- a 100% merge-rate contributor. Enriched actors (en.has_stats = 1) rank
    -- ahead of firehose-only actors since their merged-PR count is real, not a
    -- push-dominated proxy.
    ORDER BY
      (en.has_stats = 1) DESC,
      (p.prs > 0 OR en.has_stats = 1) DESC,
      (if(en.has_stats = 1, en.merged_prs, p.mergedPrs) + 1.0) / (p.prs + 1.0) DESC,
      p.repos DESC,
      p.commits DESC
    -- END issue #40 JOIN/ranking change
    LIMIT {limit: UInt32}