Indexer: referrer leaderboard rank should be dense-competitive (RANK() over SUM), not page ordinal

Brouie commented

2026-05-07 02:48:10 +00:00

(Migrated from gitlab.com)

split out from #170 follow-up — @PlasticDigits confirmed "Yes we do need to show true placement under ties".

current behavior at HEAD `284c15c`

GET /v1/referrals/referrer-leaderboard at indexer/src/api.rs:1907-1964. handler builds rank field as page ordinal:

rank: off + i as i64 + 1

so the first row of ?limit=10&offset=20 is rank 21 regardless of how many tie-groups precede it. SQL post-#170 fix is ORDER BY SUM(referrer_amount) DESC NULLS LAST, referrer ASC — pagination is deterministic but rank is page-position, not leaderboard-position.

desired: dense-competitive rank where ties share the same numeric rank and the next non-tied entry skips past them (1, 2, 2, 4 — the example you cited in the #170 follow-up).

fix shape

window function over the grouped sum, computed in the SQL layer:

SELECT referrer, total_referrer_charm_wad, referred_buy_count, rank
  FROM (
      SELECT referrer,
             COALESCE(SUM(referrer_amount), 0)::text AS total_referrer_charm_wad,
             COUNT(*)::text                          AS referred_buy_count,
             RANK() OVER (
                 ORDER BY COALESCE(SUM(referrer_amount), 0) DESC NULLS LAST
             )::bigint                               AS rank
        FROM idx_timecurve_referral_applied
       GROUP BY referrer
  ) ranked
 ORDER BY rank ASC, referrer ASC
 LIMIT $1 OFFSET $2

RANK() (not DENSE_RANK()) gives the 1, 2, 2, 4 pattern you specified. inner subquery is required because RANK() cannot reference an aggregate alias from the same SELECT. tiebreaker referrer ASC preserved at outer level for deterministic pagination across equal ranks.

handler change at :1935-1946 — drop the enumerate() ordinal, read rank from the SQL column:

let items: Vec<ReferralReferrerLeaderboardRow> = rows
    .into_iter()
    .filter_map(|r| {
        Some(ReferralReferrerLeaderboardRow {
            rank: r.try_get::<i64, _>("rank").ok()?,
            referrer: r.try_get("referrer").ok()?,
            total_referrer_charm_wad: r.try_get("total_referrer_charm_wad").ok()?,
            referred_buy_count: r.try_get("referred_buy_count").ok()?,
        })
    })
    .collect();

consumer impact

frontend ReferralLeaderboardSection.tsx:120 uses row.rank === 1 for --first styling. under ties at the top, multiple rows would now get the gold-first treatment. probably the desired UX behavior given the spec change but worth confirming.

ReferralLeaderboardSection.tsx:126 renders #{row.rank} literally — ties read #1, #1, #3 which is visually correct.

no other consumer of the rank field on this endpoint in the frontend.

schema versioning

value semantics on the rank field shift (page-ordinal → leaderboard-rank); field name unchanged. consistent with #170 / #168 precedent of bumping on response semantic changes — recommend SCHEMA_VERSION from 1.16.1 → 1.17.0 (additive semantic, no field removal). update api.rs:24.

test coverage

extend indexer/tests/integration_stage2.rs:

seed ≥3 idx_timecurve_referral_applied rows where two referrers tie on SUM(referrer_amount)
assert tied rows return equal rank values and the row directly after them has rank = tied_rank + tie_count (e.g. tied at 1 → next is 3; tied at 2 → next is 4 per your example)
pin paginated tie behavior — ?limit=2&offset=0 returns ranks [1,2], ?limit=2&offset=2 returns ranks [2,4] or whatever the seeded distribution requires

acceptance criteria

RANK() OVER (...) window function computes rank in SQL, not in handler
tied referrers return equal rank values, next rank skips by tie-count
paginated reads do not duplicate or skip referrers across tie-group boundaries
cargo test + cargo clippy --all-targets -- -D warnings clean
schema bumped

happy to ship as a small MR if you want it off your queue — SQL rewrite + handler row-mapping + schema bump + integration test addition is a tight patch.

cc @PlasticDigits

split out from #170 follow-up — @PlasticDigits confirmed "Yes we do need to show true placement under ties". ## current behavior at HEAD `284c15c` `GET /v1/referrals/referrer-leaderboard` at `indexer/src/api.rs:1907-1964`. handler builds `rank` field as page ordinal: ``` rank: off + i as i64 + 1 ``` so the first row of `?limit=10&offset=20` is rank 21 regardless of how many tie-groups precede it. SQL post-#170 fix is `ORDER BY SUM(referrer_amount) DESC NULLS LAST, referrer ASC` — pagination is deterministic but rank is page-position, not leaderboard-position. desired: dense-competitive rank where ties share the same numeric rank and the next non-tied entry skips past them (1, 2, 2, 4 — the example you cited in the #170 follow-up). ## fix shape window function over the grouped sum, computed in the SQL layer: ``` SELECT referrer, total_referrer_charm_wad, referred_buy_count, rank FROM ( SELECT referrer, COALESCE(SUM(referrer_amount), 0)::text AS total_referrer_charm_wad, COUNT(*)::text AS referred_buy_count, RANK() OVER ( ORDER BY COALESCE(SUM(referrer_amount), 0) DESC NULLS LAST )::bigint AS rank FROM idx_timecurve_referral_applied GROUP BY referrer ) ranked ORDER BY rank ASC, referrer ASC LIMIT $1 OFFSET $2 ``` `RANK()` (not `DENSE_RANK()`) gives the 1, 2, 2, 4 pattern you specified. inner subquery is required because `RANK()` cannot reference an aggregate alias from the same SELECT. tiebreaker `referrer ASC` preserved at outer level for deterministic pagination across equal ranks. handler change at `:1935-1946` — drop the `enumerate()` ordinal, read `rank` from the SQL column: ``` let items: Vec<ReferralReferrerLeaderboardRow> = rows .into_iter() .filter_map(|r| { Some(ReferralReferrerLeaderboardRow { rank: r.try_get::<i64, _>("rank").ok()?, referrer: r.try_get("referrer").ok()?, total_referrer_charm_wad: r.try_get("total_referrer_charm_wad").ok()?, referred_buy_count: r.try_get("referred_buy_count").ok()?, }) }) .collect(); ``` ## consumer impact frontend `ReferralLeaderboardSection.tsx:120` uses `row.rank === 1` for `--first` styling. under ties at the top, multiple rows would now get the gold-first treatment. probably the desired UX behavior given the spec change but worth confirming. `ReferralLeaderboardSection.tsx:126` renders `#{row.rank}` literally — ties read `#1, #1, #3` which is visually correct. no other consumer of the `rank` field on this endpoint in the frontend. ## schema versioning value semantics on the `rank` field shift (page-ordinal → leaderboard-rank); field name unchanged. consistent with #170 / #168 precedent of bumping on response semantic changes — recommend `SCHEMA_VERSION` from `1.16.1` → `1.17.0` (additive semantic, no field removal). update `api.rs:24`. ## test coverage extend `indexer/tests/integration_stage2.rs`: - seed ≥3 `idx_timecurve_referral_applied` rows where two referrers tie on `SUM(referrer_amount)` - assert tied rows return equal `rank` values and the row directly after them has `rank = tied_rank + tie_count` (e.g. tied at 1 → next is 3; tied at 2 → next is 4 per your example) - pin paginated tie behavior — `?limit=2&offset=0` returns ranks `[1,2]`, `?limit=2&offset=2` returns ranks `[2,4]` or whatever the seeded distribution requires ## acceptance criteria - [ ] `RANK() OVER (...)` window function computes rank in SQL, not in handler - [ ] tied referrers return equal `rank` values, next rank skips by tie-count - [ ] paginated reads do not duplicate or skip referrers across tie-group boundaries - [ ] `cargo test` + `cargo clippy --all-targets -- -D warnings` clean - [ ] schema bumped happy to ship as a small MR if you want it off your queue — SQL rewrite + handler row-mapping + schema bump + integration test addition is a tight patch. cc @PlasticDigits

Brouie commented

2026-05-07 02:49:02 +00:00

(Migrated from gitlab.com)