Devlog 15: Building Real-Time Leaderboards That Update Live

Leaderboards sound simple: just rank users by votes and display them. But when thousands of people are voting simultaneously and you need sub-second updates, things get complicated.

I wanted leaderboards that feel alive, updating in real-time as votes pour in, showing both anonymous and authenticated users, and handling edge cases like ties gracefully.

The Basic Query

Start with the simplest version:

SELECT
  COALESCE(user_id::text, device_id) as voter_id,
  SUM(amount) as total_votes
FROM votes
WHERE matchup_id = $1 AND side = $2
GROUP BY voter_id
ORDER BY total_votes DESC
LIMIT 10;

This works but is missing key features: display names, rankings, ties.

Adding Display Names

Anonymous users need names like "Anonymous #abc123", authenticated users show their username:

SELECT
  COALESCE(user_id::text, device_id) as voter_id,
  COALESCE(
    (SELECT username FROM auth.users WHERE id = votes.user_id),
    'Anonymous #' || LEFT(device_id, 8)
  ) as display_name,
  SUM(amount) as total_votes
FROM votes
WHERE matchup_id = $1 AND side = $2
GROUP BY voter_id, user_id, device_id
ORDER BY total_votes DESC
LIMIT 10;

The nested SELECT is necessary because user_id might be NULL for anonymous voters.

Window Functions for Ranking

I want to show the actual rank (1st, 2nd, 3rd), handling ties properly:

WITH voter_totals AS (
  SELECT
    COALESCE(user_id::text, device_id) as voter_id,
    COALESCE(
      (SELECT username FROM auth.users WHERE id = votes.user_id),
      'Anonymous #' || LEFT(device_id, 8)
    ) as display_name,
    SUM(amount) as total_votes
  FROM votes
  WHERE matchup_id = $1 AND side = $2
  GROUP BY voter_id, user_id, device_id
)
SELECT
  voter_id,
  display_name,
  total_votes,
  RANK() OVER (ORDER BY total_votes DESC) as rank
FROM voter_totals
ORDER BY total_votes DESC
LIMIT 10;

RANK() handles ties correctly: if two users have 100 votes each, they both get rank 1, and the next user gets rank 3 (not 2).

Performance Problem: N+1 Queries

The nested SELECT for username creates an N+1 query problem. For 10 leaderboard entries, that's 10 extra queries.

Solution: JOIN instead of nested SELECT:

WITH voter_totals AS (
  SELECT
    COALESCE(v.user_id::text, v.device_id) as voter_id,
    v.user_id,
    v.device_id,
    SUM(v.amount) as total_votes
  FROM votes v
  WHERE v.matchup_id = $1 AND side = $2
  GROUP BY voter_id, v.user_id, v.device_id
)
SELECT
  vt.voter_id,
  COALESCE(u.username, 'Anonymous #' || LEFT(vt.device_id, 8)) as display_name,
  vt.total_votes,
  RANK() OVER (ORDER BY vt.total_votes DESC) as rank
FROM voter_totals vt
LEFT JOIN auth.users u ON vt.user_id = u.id
ORDER BY vt.total_votes DESC
LIMIT 10;

Now it's a single query with one JOIN. Much faster.

Real-Time Updates

Leaderboards need to update as votes come in. I subscribe to vote changes via Supabase Realtime:

supabase
  .channel(`matchup:${matchupId}:leaderboard`)
  .on('postgres_changes',
    {
      event: '*',
      schema: 'public',
      table: 'votes',
      filter: `matchup_id=eq.${matchupId}`
    },
    () => {
      refreshLeaderboard();
    }
  )
  .subscribe();

Any vote INSERT/UPDATE/DELETE triggers a leaderboard refresh. But refreshing on every vote is too expensive.

Throttling Updates

I throttle leaderboard refreshes to once per 2 seconds:

let refreshPending = false;

function refreshLeaderboard() {
  if (refreshPending) return;

  refreshPending = true;
  setTimeout(async () => {
    const data = await fetchLeaderboard();
    updateLeaderboardUI(data);
    refreshPending = false;
  }, 2000);
}

This batches multiple vote events into a single leaderboard query. Cuts load by ~95%.

Highlighting Current User

If you're on the leaderboard, your entry should be highlighted:

{#each leaderboard as entry}
  <div class:highlighted={entry.voter_id === currentVoterId}>
    <span class="rank">#{entry.rank}</span>
    <span class="name">{entry.display_name}</span>
    <span class="votes">{entry.total_votes.toLocaleString()}</span>
  </div>
{/each}

For anonymous users, I compare against device_id. For authenticated, against user_id.

Showing "You" Even When Not Top 10

If you're not in top 10, I still want to show your rank:

-- Get top 10
WITH top_10 AS (...)

-- Get current user rank
, current_user_rank AS (
  SELECT
    RANK() OVER (ORDER BY SUM(amount) DESC) as rank,
    SUM(amount) as total_votes
  FROM votes
  WHERE matchup_id = $1 AND side = $2
    AND (user_id = $3 OR device_id = $4)
  GROUP BY COALESCE(user_id::text, device_id)
)

SELECT * FROM top_10
UNION ALL
SELECT * FROM current_user_rank;

Shows top 10 plus your own ranking even if you're #147.

Handling Rank Changes

When your rank changes, I animate it:

<script>
  import { tweened } from 'svelte/motion';
  import { cubicOut } from 'svelte/easing';

  const rankPosition = tweened(0, { duration: 300, easing: cubicOut });

  $: rankPosition.set(entry.rank);
</script>

<div style="transform: translateY({$rankPosition * 60}px)">
  <!-- Entry content -->
</div>

Ranks smoothly slide up/down instead of jumping.

Caching Strategy

Leaderboards are expensive to compute but change slowly. I cache them:

const cache = new Map();

async function getLeaderboard(matchupId: string, side: string) {
  const key = `${matchupId}:${side}`;
  const cached = cache.get(key);

  if (cached && Date.now() - cached.timestamp < 5000) {
    return cached.data;
  }

  const data = await queryLeaderboard(matchupId, side);
  cache.set(key, { data, timestamp: Date.now() });
  return data;
}

5-second cache means most requests hit cache, not database.

Edge Cases

Ties across page boundary:

If users ranked 10 and 11 both have 500 votes, do I show both? Or cut off at 10?

I chose to cut at 10 for simplicity, but show tied rank numbers (both are "10th").

Zero votes:

New matchups have empty leaderboards. I show a friendly message:

{#if leaderboard.length === 0}
  <div class="empty-state">
    Be the first to vote!
  </div>
{:else}
  <!-- Leaderboard entries -->
{/if}

Switching sides:

If you switch from voting A to voting B, your rank disappears from A's leaderboard and appears on B's. The transition is jarring.

I fade out the old rank and fade in the new one to smooth this.

Takeaway

Real-time leaderboards are trickier than they look. Efficient SQL, caching, throttling, and smooth animations all matter.

PostgreSQL's window functions make ranking straightforward. RANK() handles ties correctly without special logic.

Batching realtime updates is essential. Don't requery on every single vote. Throttle to 2-5 seconds.

The dual attribution pattern (user_id OR device_id) complicates queries slightly but LEFT JOIN handles it cleanly.

Next I'm tackling image uploads and storage with Supabase.