ranking

Postgres Ranking Query

This package provides the native Postgres SQL query for computing the commitgraph leaderboard ranking with identity resolution before aggregation.

Core Principle

Identity resolution happens BEFORE ranking, not after.

You cannot rank first and merge identities afterward. When one person's commits sit under two login keys, ranking before the merge produces two wrong rows. Rank must be computed AFTER alias resolution.

Query Structure

The query in postgres_ranking_query.sql is structured as reusable CTEs:

1. user_with_login: Join repo_user_daily_tool to users to get login for each rollup row
2. canonical_login: Apply user_aliases merge to resolve to canonical login (the AGG-CORE foundation)
3. window_anchor: Define the 30-day window anchor (CURRENT_DATE - 29 days)
4. aggregated_commits: Aggregate by canonical login over the 30-day window

The final SELECT applies RANK() OVER after all aggregation is complete.

Acceptance Criteria

All criteria are met by the query:

• Query resolves login→canonical login via user_aliases in the same query/CTE, before RANK() is applied
• Two distinct logins aliasing to the same canonical login produce ONE ranked row with summed commits
• 30-day window is anchored at current_date - 29, identical for every row (not per-user MAX(day))
• Query runs entirely against Postgres; no export-then-query step in the computation path
• Query/CTE structure is reusable as the basis for histogram, tiebreak, and scan-recency queries

Schema Relationship

The query works with the current schema where repo_user_daily_tool stores user_id (not email):

repo_user_daily_tool.user_id → users.user_id → users.login
                                                      ↓
                                        user_aliases.source_login
                                                      ↓
                                        user_aliases.target_login (canonical)

Note: Email→login resolution happens at ingest time (when user_id is assigned from the author email). At query time, we apply the user_aliases merge to resolve to canonical login before ranking.

Extensibility

The AGG-CORE CTEs (canonical_login, window_anchor, aggregated_commits) are designed to be reused for:

1. Ranking: Add RANK() OVER (ORDER BY ai_commits_30d DESC, ...)
2. Histogram: Add PERCENTILE_CONT(0.50) aggregations for percentile distribution
3. Tiebreak: Add recency scoring (SUM(commits * age_in_days)) and include in ORDER BY
4. Scan-recency: Add day distribution analysis

See the query comments for examples of how to extend it for these use cases.

30-Day Window Semantics

The 30-day window is board-wide, not per-user:

SELECT CURRENT_DATE - INTERVAL '29 days' AS window_start

Every row uses the same anchor (CURRENT_DATE - 29), which means:

• The window is identical for all users on a given day
• Users who haven't committed recently may have 0 commits in the window
• The window advances at midnight UTC each day

This is different from a "trailing 30 days per user" which would use MAX(day) - INTERVAL '30 days' per user.

RANK vs DENSE_RANK vs ROW_NUMBER

The query returns three different ranking functions:

• rank_30d (DENSE_RANK): Consecutive ranks (1, 2, 3, ...) even with ties
  • Use this for leaderboard display where you want compact ranks
• standard_rank_30d (RANK): Same rank to ties, then skips (1, 1, 3, ...)
  • Use this to understand how many users are tied at each rank
• row_num (ROW_NUMBER): Unique consecutive number (1, 2, 3, ...)
  • Use this for pagination and stable ordering

Usage

To execute the query:

psql -h <host> -U <user> -d <database> -f pkg/ranking/postgres_ranking_query.sql

Or from an application using the PostgreSQL driver:

// Example: load the query from the file
query, err := os.ReadFile("pkg/ranking/postgres_ranking_query.sql")
if err != nil {
    return err
}
rows, err := db.Query(string(query))

Architecture Reference

This query implements the architecture described in docs/plan/plan.md:

• Line 358-361: "SQL rollup joined to user_aliases in the same database, so the alias merge is a real join evaluated before RANK() rather than a post-hoc pass"
• Line 387-401: "Postgres computes the ranking, not DuckDB... Rank/30-day-window/tiebreak/percentile-distribution all run as native Postgres SQL (window functions, RANK() OVER, PERCENTILE_CONT) against the live rollup"
• Line 412-426: "Rank must be computed after alias resolution, so the identity data has to live wherever ranking happens. That is Postgres."

Per-User 30-Day Histogram Query

The per_user_30day_histogram.sql query computes a dense 30-element integer array for each user, representing AI commits per day over the trailing 30 days.

Key properties:

• Board-wide window: current_date - 29 .. current_date (identical for every user)
• Dense array: Index 0 = oldest day, index 29 = current_date, zeros for inactive days
• Single query: One GROUP BY user_id, day for the whole board (not N per-user queries)
• Full list scope: Computed for all ranked rows per decision cg-1gx
• Exclusion honoured: repos.excluded_at IS NULL filter applied
• Alias merge: Uses the same AGG-CORE CTEs as the ranking query

Output schema:

canonical_login TEXT    -- User's canonical login (after alias merge)
daily_ai_commits INT[] -- 30-element array, index 0 = oldest day

File-level metadata (to be added by Parquet writer):

window_start DATE       -- Start of the 30-day window (added to Parquet metadata, not per-row)

Consumer usage: Reconstruct dates from window_start + index:

• Index 0 corresponds to window_start
• Index 29 corresponds to window_start + 29 days (current_date)

Usage: The query can be joined with the ranking query on canonical_login to produce a single result set with rank + histogram.

Decision reference: See docs/notes/cg-1gx-histogram-scope-decision.md for the rationale behind computing the histogram for the full list rather than a top-N subset.

See Also

• docs/plan/plan.md - Full architecture specification
• docs/notes/postgres-computes-the-ranking.md - Rationale for Postgres-native ranking
• docs/research/claude-leaderboard-comparison.md - Predecessor system comparison
• docs/notes/cg-1gx-histogram-scope-decision.md - Histogram scope decision (full list vs top-N)