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

split out from #170 (closed) 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 (closed) 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 (closed) / #168 (closed) 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