Design a 500M-User Weekly Leaderboard

Prototype a scalable cohort leaderboard and defend its distributed design.

Introduction

30 Second Summary

A weekly leaderboard feels simple when you only see your rank. At massive scale, every score update must survive retries without overwhelming one shared ranking.

In this project, you will prototype a weekly cohort leaderboard for 500 million registered users in Google Sheets. You will expose its correctness and scaling failures before redesigning it as an event-driven system in draw.io.

What You'll Build

Picture presenting a working cohort leaderboard beside its production architecture during a 90-second system design interview.

By the end of this project, you'll have:

  • A deduplicated leaderboard prototype that rejects repeated event IDs before they inflate a user's weekly XP total.
  • A capacity model that turns explicit assumptions into peak traffic, cohort count, and storage estimates for 500 million registered users.
  • A production architecture and interview defense covering bounded cohort rankings, durable recovery, eventual consistency, dead-letter handling, replay, and weekly rollover.
  • Secret Mission: Extend the diagram with a second region while preserving one controlled score-writing path.

Are there any prerequisites?

You need a basic grasp of system design plus access to Google Sheets in a modern browser. You do not need an AWS account or local runtime because the cloud services remain architecture components.

Before We Start

This checkpoint confirms that you can access Google Sheets in a modern browser. It also locks in the design target you will defend before you prototype its rebuildable Valkey ranking view.

Set Up the Design Workspace

Capacity estimates can drift away from a diagram when each artifact lives in a different workspace. This step creates one workspace so your prototype, calculations, failure analysis, and interview narrative stay consistent.

Google Sheets will hold the evidence behind your design decisions. draw.io will turn those decisions into the architecture you present.

In this step, get ready to:
  • Create a workbook named 500M Weekly Leaderboard.
  • Organize the workbook into six named tabs.
  • Add a Leaderboard API box to an empty draw.io diagram.
Create the leaderboard workbook

The workbook becomes the source of truth for every number you calculate later. A clear name makes it easy to keep the prototype connected to its architecture.

  • Complete the Google account sign-in flow if Google Sheets asks for it.
  • Create a blank spreadsheet from the Google Sheets start page.
  • Click the current workbook title in the upper-left corner.
  • Enter 500M Weekly Leaderboard in the title field.
  • Press Enter to save the workbook name.

You should see 500M Weekly Leaderboard in the upper-left corner of the spreadsheet.

The six tabs separate your calculations from your prototype. They also reserve dedicated spaces for failure analysis and your interview defense.

  • Double-click the first sheet tab at the bottom of the workbook.
  • Enter Assumptions as the tab name.
  • Press Enter to apply the new name.
  • Click the plus control at the bottom five times to add five sheet tabs.
  • Rename the five new tabs from left to right as Events, Scores, Leaderboard, Failure Lab, and Defense.
  • Leave all six tabs empty for now.
  • Check that the tabs appear in the required left-to-right order.

Good. Your workbook now has a dedicated home for every piece of evidence you will build.

Why Split the Workbook?

The Assumptions tab will hold the inputs and calculations behind your capacity estimates.

The Events, Scores, and Leaderboard tabs will hold the working prototype.

The Failure Lab tab will capture recovery behavior for different failure scenarios.

The Defense tab will turn your decisions into a concise interview narrative.

Start the draw.io diagram

Your spreadsheet proves how the leaderboard behaves. The diagram will show how a production system moves events through durable storage and a ranked read model.

  • Open a new browser tab.
  • Enter https://app.diagrams.net/ in the address bar.
  • Press Enter to load the editor.
  • Choose local device storage if the editor asks where to store diagram files.
  • Start an empty diagram from the new-diagram screen.

You should see a drawing canvas with shapes and connectors available around it.

  • Drag a rectangle from the shapes area onto the empty canvas.
  • Double-click the rectangle to edit its text.
  • Enter Leaderboard API inside the rectangle.
  • Click an empty area of the canvas to finish editing the box.
  • Open File from the editor menu.
  • Save the diagram to your device as architecture.drawio.

You should see one box labeled Leaderboard API on the canvas. That first component gives the architecture a clear starting point.

Canvas or Box Missing?

  • Reload the browser tab if the drawing canvas does not appear.
  • Double-click the rectangle again if the label is missing.
  • Save the source file again from the File menu if architecture.drawio is missing from your device.
  • Still stuck? Help me set up my empty draw.io canvas and label the first box.
Verify the workspace side by side

Seeing the workbook beside the diagram makes naming mismatches easy to catch. This final check confirms that both parts of your workspace are ready.

Before you compare the two windows, do you expect every workbook tab and the API box to match the names in your design target?

  • Drag the draw.io tab away from the browser tab bar until it becomes a separate window.
  • Place the Google Sheets window on the left side of your screen.
  • Place the draw.io window on the right side of your screen.
  • Confirm that the workbook title is 500M Weekly Leaderboard.
  • Confirm that the six tabs appear as Assumptions, Events, Scores, Leaderboard, Failure Lab, and Defense.
  • Confirm that the diagram contains one box labeled Leaderboard API.

You should see the named workbook with all six empty tabs on the left. You should see the Leaderboard API box on the draw.io canvas to the right.

Your design workspace is ready. Next, you will use it to build the first working leaderboard and test its assumptions.

Build the Naive Leaderboard

Your design workspace is ready. Now you can give the leaderboard real event data in Google Sheets.

The straightforward version trusts every matching event row. A retried event can therefore increase a score twice.

A single global order can also mix separate cohorts. You will run both assumptions so their failures become visible.

In this step, get ready to:
  • Populate the event log with seven XP deliveries.
  • Calculate naive weekly totals for five users.
  • Sort every user into one global ranking to reveal both design problems.
Populate the source data

The event log represents delivered XP changes. The roster lists the users whose weekly totals you want to calculate.

  • Switch back to the Events tab in the workbook from earlier.
  • Select cell A1.
  • Paste the tab-separated event log below:
event_id	user_id	cohort_id	week_id	xp_delta
evt-001	u-101	cohort-A	2026-W42	20
evt-002	u-102	cohort-A	2026-W42	15
evt-003	u-101	cohort-A	2026-W42	10
evt-002	u-102	cohort-A	2026-W42	15
evt-004	u-103	cohort-A	2026-W42	30
evt-005	u-201	cohort-B	2026-W42	25
evt-006	u-202	cohort-B	2026-W42	40

How Is the Event Log Structured?

  • The event_id column identifies each XP event.
  • The user_id column identifies the user receiving XP.
  • The cohort_id column identifies the user's competition group.
  • The week_id column keeps the score inside one weekly window.
  • The xp_delta column records the XP awarded by the event.
  • Confirm that the pasted range fills cells A1:E8.

You should see five headers followed by seven event rows.

Data in the Wrong Columns?

  • Undo the paste if all values appear in one column.
  • Select A1 before pasting the complete table again.
  • Still stuck? Help me paste this tab-separated event log into five Google Sheets columns.

The score calculation also needs one row for every user. This roster provides the user, cohort, and week that each total must match.

  • Switch to the Scores tab.
  • Select cell A1.
  • Paste the tab-separated roster below:
user_id	cohort_id	week_id
u-101	cohort-A	2026-W42
u-102	cohort-A	2026-W42
u-103	cohort-A	2026-W42
u-201	cohort-B	2026-W42
u-202	cohort-B	2026-W42

What Does the Roster Control?

Each row defines one score to calculate. Matching on the user, cohort, and week prevents XP from a different competition window from entering that total.

  • Confirm that the roster fills cells A1:C6.

You should see three headers followed by five users across two cohorts.

Roster Shifted Out of Place?

  • Clear the pasted roster if its first header does not begin in A1.
  • Select A1 before pasting the complete roster again.
  • Still stuck? Help me place this five-user roster in Scores cells A1:C6.
Calculate naive weekly totals

A naive weekly total adds every XP row that matches the same user, cohort, and week. The formula has no memory of which event IDs it has already counted.

  • Enter naive_total_xp in cell D1.
  • Select cell D2.
  • Calculate the first user's total by pasting this formula:
=SUMIFS(Events!$E$2:$E,Events!$B$2:$B,A2,Events!$C$2:$C,B2,Events!$D$2:$D,C2)

What Does This Formula Do?

  • The SUMIFS function adds values from the xp_delta column.
  • The first condition matches the user in A2.
  • The second condition matches the cohort in B2.
  • The third condition matches the week in C2.
  • Inspect the result in D2.

You should see 30 because user u-101 has two matching XP events worth 20 and 10.

  • Drag the small square at D2's lower-right corner down to D6.

Before you inspect the copied results, do you expect the repeated event ID to contribute XP once or twice?

  • Select cell D3.

You should see 30 in Scores!D3. The two evt-002 rows each add 15 XP.

This shortfall is intentional. The formula matches user, cohort, and week values without recognizing a previously processed event ID.

Totals Missing or Unexpected?

  • Confirm that the formula begins in D2.
  • Confirm that the event data still fills Events!A1:E8.
  • Confirm that the formula has been copied through D6.
  • Still stuck? Help me troubleshoot the naive SUMIFS totals in Scores D2:D6.
Create the global ranking

Sorting the totals produces a leaderboard view. This version has no cohort selector or cohort filter.

Before you add the ranking formula, do you expect one sort to preserve each cohort's separate competition?

  • Switch to the Leaderboard tab.
  • Select cell A5.
  • Create the descending ranking by pasting this formula:
=SORT(Scores!A2:D,4,FALSE)

What Does This Formula Do?

  • The SORT function reads columns A:D from the Scores tab.
  • The 4 selects naive_total_xp as the sort column.
  • The FALSE value places larger totals before smaller totals.
  • Inspect the rows that fill cells below A5:D.

You should see users from cohort-A and cohort-B in one ordered list. User u-202 from cohort-B appears above the cohort-A users because every score shares one sort.

Ranking Not Filling the Sheet?

  • Clear any existing values from the cells below A5:D.
  • Confirm that the ranking formula begins in A5.
  • Confirm that Scores!A2:D6 contains the five roster rows.
  • Still stuck? Help me troubleshoot the global SORT formula in Leaderboard A5.

✔️ Awesome, I've got everything!

Great. Your three populated tabs now match the naive leaderboard prototype.

ⓧ I'd like to double check the full code

  • Compare each populated tab with the references below.
Events tab
event_id	user_id	cohort_id	week_id	xp_delta
evt-001	u-101	cohort-A	2026-W42	20
evt-002	u-102	cohort-A	2026-W42	15
evt-003	u-101	cohort-A	2026-W42	10
evt-002	u-102	cohort-A	2026-W42	15
evt-004	u-103	cohort-A	2026-W42	30
evt-005	u-201	cohort-B	2026-W42	25
evt-006	u-202	cohort-B	2026-W42	40

What Should Match?

Your Events tab should contain five columns. It should end with evt-006 in row 8.

Scores tab
user_id	cohort_id	week_id	naive_total_xp
u-101	cohort-A	2026-W42	=SUMIFS(Events!$E$2:$E,Events!$B$2:$B,A2,Events!$C$2:$C,B2,Events!$D$2:$D,C2)
u-102	cohort-A	2026-W42	=SUMIFS(Events!$E$2:$E,Events!$B$2:$B,A3,Events!$C$2:$C,B3,Events!$D$2:$D,C3)
u-103	cohort-A	2026-W42	=SUMIFS(Events!$E$2:$E,Events!$B$2:$B,A4,Events!$C$2:$C,B4,Events!$D$2:$D,C4)
u-201	cohort-B	2026-W42	=SUMIFS(Events!$E$2:$E,Events!$B$2:$B,A5,Events!$C$2:$C,B5,Events!$D$2:$D,C5)
u-202	cohort-B	2026-W42	=SUMIFS(Events!$E$2:$E,Events!$B$2:$B,A6,Events!$C$2:$C,B6,Events!$D$2:$D,C6)

What Should Match?

Your Scores tab should contain five users. Column D should contain a row-adjusted formula for each user.

Leaderboard tab
=SORT(Scores!A2:D,4,FALSE)

What Should Match?

Your Leaderboard tab should contain this formula in A5.

Before the final check, do you expect either failure to disappear without changing the formulas?

  • Return to the Scores tab.
  • Inspect cell D3.
  • Return to the Leaderboard tab.
  • Inspect the cohort values in the global ranking.

You should see 30 in Scores!D3. You should also see both cohorts in the same leaderboard.

That is a useful failure. Your prototype now proves duplicate counting and cohort mixing with visible spreadsheet results.

Your naive leaderboard works well enough to expose its limits. Next, you will quantify how those limits behave at 500 million registered users.

Quantify the Scale Bottleneck

Your naive leaderboard now shows u-102 with 30 XP from one unique 15 XP event. Its global order also mixes both cohorts.

Capacity planning in Google Sheets now tests whether that global shape survives 500 million registered users. A Valkey sorted set supports logarithmic updates.

One global key still occupies one hash slot. Its memory stays on one shard.

Every ranking request reaches that shard. A failure there affects the entire leaderboard.

In this step, get ready to:
  • Enter explicit workload assumptions for the 500-million-user scenario.
  • Calculate traffic rates, cohort counts, and storage estimates.
  • Document the global sorted-set bottleneck on the architecture diagram.
Enter the scenario assumptions

A capacity model starts with assumptions that anyone can inspect or change. These values describe the exercise scenario instead of measured production traffic.

  • Switch back to the Assumptions tab from earlier.
  • Select cell A2.
  • Populate cells A2:B12 by pasting this tab-separated block:
Registered users	500000000
DAU rate	10%
XP events per DAU per day	20
XP peak factor	12
Leaderboard reads per DAU per day	8
Read peak factor	10
Active users per cohort	30
Assumed bytes per XP event	200
Assumed bytes per weekly score item	100
Assumed bytes per sorted-set member	64
Cache overhead factor	2

What Do These Assumptions Represent?

  • Registered users defines the maximum population considered by the design.
  • The DAU rate estimates how many registered users participate on a typical day.
  • The event and read rates describe activity from each daily active user.
  • The peak factors model short periods when traffic rises above the daily average.
  • The cohort size creates a bounded competition group for each active user.
  • The byte values are planning assumptions for comparing storage shapes.
  • Pause until Google Sheets finishes autosaving the workbook.
  • Confirm the assumption labels fill cells A2:A12.
  • Confirm the corresponding values fill cells B2:B12.

You should see eleven labeled assumptions with editable values beside them.

Seeing Shifted Assumptions?

  • Check that Registered users begins in cell A2.
  • Undo the paste if every value landed in one column.
  • Paste the tab-separated block into A2 again.

Still stuck? Help me place the assumptions correctly in Google Sheets.

Calculate traffic and storage

The output formulas translate the assumptions into daily volume, peak throughput, cohort count, and storage. Keeping every input visible makes the model easy to challenge during a design review.

  • Select cell D2 in the Assumptions tab.
  • Calculate the capacity outputs in D2:E14 by pasting this tab-separated block:
Daily active users	=B2*B3
XP events per day	=E2*B4
Average XP events per second	=E3/86400
Peak XP events per second	=E4*B5
Leaderboard reads per day	=E2*B6
Average leaderboard reads per second	=E6/86400
Peak leaderboard reads per second	=E7*B7
Weekly cohorts	=ROUNDUP(E2/B8,0)
Event storage, decimal GB per day	=E3*B9/1000000000
Seven-day event storage, decimal TB	=E10*7/1000
Weekly score storage, decimal GB	=E2*B10/1000000000
Active cache, decimal GB	=E2*B11*B12/1000000000
Naive global cache, decimal GB	=B2*B11*B12/1000000000

How Does the Model Work?

  • Cell E2 applies the DAU rate to the registered-user population.
  • Cells E4 and E7 divide daily totals across 86,400 seconds.
  • Cells E5 and E8 apply the supplied peak factors.
  • Cell E9 rounds the cohort count upward so every active user has a cohort.
  • Cells E10:E14 turn record counts into decimal storage estimates.

Before you inspect the results, predict which output grows fastest when the DAU rate increases.

  • Inspect the daily active user result in cell E2.
  • Inspect the peak XP event rate in cell E5.
  • Inspect the peak leaderboard read rate in cell E8.
  • Inspect the weekly cohort count in cell E9.
  • Inspect the storage estimates in cells E10:E14.

What Should I See?

  • Cell E2 shows 50,000,000 daily active users.
  • Cell E5 shows approximately 138,889 peak XP events per second.
  • Cell E8 shows approximately 46,296 peak leaderboard reads per second.
  • Cell E9 shows 1,666,667 weekly cohorts.
  • Cell E10 shows 200 decimal GB of event records per day.
  • Cell E11 shows 1.4 decimal TB of event records across seven days.
  • Cell E12 shows 5 decimal GB of weekly score records.
  • Cell E13 shows 6.4 decimal GB for the active cache estimate.
  • Cell E14 shows 64 decimal GB for the naive global cache estimate.

That's the scale model working: your workbook now quantifies the 500-million-user workload.

Seeing Formula Errors?

  • Check that the input values occupy cells B2:B12.
  • Check that the first output formula begins in cell E2.
  • Remove any leading apostrophe if a formula appears as plain text.

Still stuck? Help me diagnose the capacity formulas in my Assumptions tab.

✔️ Awesome, I've Got Everything

Your Assumptions tab now contains the complete capacity model. Google Sheets keeps the formulas linked to the editable scenario inputs.

ⓧ I'd Like to Double Check the Full Code

A2: Registered users	B2: 500000000
A3: DAU rate	B3: 10%
A4: XP events per DAU per day	B4: 20
A5: XP peak factor	B5: 12
A6: Leaderboard reads per DAU per day	B6: 8
A7: Read peak factor	B7: 10
A8: Active users per cohort	B8: 30
A9: Assumed bytes per XP event	B9: 200
A10: Assumed bytes per weekly score item	B10: 100
A11: Assumed bytes per sorted-set member	B11: 64
A12: Cache overhead factor	B12: 2
D2: Daily active users	E2: =B2*B3
D3: XP events per day	E3: =E2*B4
D4: Average XP events per second	E4: =E3/86400
D5: Peak XP events per second	E5: =E4*B5
D6: Leaderboard reads per day	E6: =E2*B6
D7: Average leaderboard reads per second	E7: =E6/86400
D8: Peak leaderboard reads per second	E8: =E7*B7
D9: Weekly cohorts	E9: =ROUNDUP(E2/B8,0)
D10: Event storage, decimal GB per day	E10: =E3*B9/1000000000
D11: Seven-day event storage, decimal TB	E11: =E10*7/1000
D12: Weekly score storage, decimal GB	E12: =E2*B10/1000000000
D13: Active cache, decimal GB	E13: =E2*B11*B12/1000000000
D14: Naive global cache, decimal GB	E14: =B2*B11*B12/1000000000
Document the one-key bottleneck

The spreadsheet shows the workload size. The draw.io diagram needs to show where that workload concentrates.

  • Switch back to the draw.io canvas from earlier.
  • Drag a rectangle from the shapes panel near the existing Leaderboard API box.
  • Enter Naive global sorted set: 500M members, one key, one hash slot inside the rectangle.
  • Set the rectangle to a red fill using the formatting controls.
  • Add a text box beneath the red rectangle from the shapes panel.
  • Enter Hot key, concentrated memory, and one-shard blast radius. inside the text box.
  • Save the existing architecture.drawio file using the File menu.

Why Is One Global Key a Bottleneck?

Valkey Cluster assigns each key to one hash slot. A large sorted set remains one value owned by the shard serving that slot.

A ZADD update can run in O(log(N)) time. That efficiency does not distribute one key across several shards.

The owning shard carries the memory load. It also receives the traffic for that key.

A problem on that shard affects the entire global ranking. Cohort-sized keys create smaller failure boundaries.

Before you check both artifacts, predict whether logarithmic operations allow one global key to spread across multiple hash slots.

  • Position Google Sheets on one side of your screen.
  • Position draw.io on the other side of your screen.
  • Confirm the four core capacity outputs in the Assumptions tab.
  • Confirm the red global sorted-set callout remains visible on the canvas.
  • Confirm the hot-key note remains visible beneath the callout.

What Should the Final Check Show?

  • The model shows 50,000,000 daily active users.
  • The model shows approximately 138,889 peak XP events per second.
  • The model shows approximately 46,296 peak leaderboard reads per second.
  • The model shows 1,666,667 weekly cohorts.
  • The diagram shows Naive global sorted set: 500M members, one key, one hash slot in red.
  • The diagram explains the hot key, concentrated memory, and one-shard blast radius.

You have turned a vague scale concern into numbers and a visible architecture bottleneck. Your design can now explain exactly why the global ranking shape fails.

Your bottleneck is now measurable. Next, you'll partition the ranking work and make score updates safe against duplicate events.

Build the Production Leaderboard

Your capacity model exposed the single-key bottleneck. A production leaderboard must spread ranking work across cohort-and-week keys.

Retries can repeat the same XP event. The durable design must reject duplicate event IDs while keeping weekly totals available to rebuild Valkey.

In this step, get ready to:
  • Reject repeated event IDs in the spreadsheet prototype.
  • Calculate durable totals plus cohort ranks.
  • Draw the production data paths.
Make score updates idempotent

In Google Sheets, the first_seen column models event deduplication. Only the first row for each event ID contributes to the durable total.

  • Switch back to the Events tab.
  • Enter first_seen in cell F1.
  • Select cell F2.
  • Mark the first occurrence of each event ID by entering this formula:
=COUNTIF($A$2:A2,A2)=1

What Does This Formula Prove?

  • COUNTIF counts the current event ID from the first data row through the current row.
  • The first occurrence returns TRUE. A repeated occurrence returns FALSE.
  • This flag gives the score formula a reliable way to ignore retries.
  • Extend the formula from F2 through F8 with the fill handle.
  • Inspect the two rows containing evt-002.

Good. The first evt-002 row shows TRUE. The repeated row shows FALSE.

Duplicate Row Still Shows TRUE?

Check that the range begins with $A$2. The locked starting row lets the count include every earlier event.

Confirm the formula in each row ends with that row's event cell. Help me fix the first_seen formula in Google Sheets.

✔️ Awesome, I've got everything!

Your event log now identifies the repeated event without deleting its audit record.

ⓧ I'd like to double check the full code

event_id	user_id	cohort_id	week_id	xp_delta	first_seen
evt-001	u-101	cohort-A	2026-W42	20	=COUNTIF($A$2:A2,A2)=1
evt-002	u-102	cohort-A	2026-W42	15	=COUNTIF($A$2:A3,A3)=1
evt-003	u-101	cohort-A	2026-W42	10	=COUNTIF($A$2:A4,A4)=1
evt-002	u-102	cohort-A	2026-W42	15	=COUNTIF($A$2:A5,A5)=1
evt-004	u-103	cohort-A	2026-W42	30	=COUNTIF($A$2:A6,A6)=1
evt-005	u-201	cohort-B	2026-W42	25	=COUNTIF($A$2:A7,A7)=1
evt-006	u-202	cohort-B	2026-W42	40	=COUNTIF($A$2:A8,A8)=1

The durable total uses first_seen as an extra condition. The rank formulas then compare each durable total against the relevant users.

  • Switch back to the Scores tab.
  • Select cell E1.
  • Add the three result columns by pasting these headers:
durable_total_xp	global_rank	cohort_rank

What Do These Columns Measure?

  • durable_total_xp includes only events marked as their first occurrence.
  • global_rank preserves the broad comparison that exposed the original design problem.
  • cohort_rank compares users inside the same cohort for the same week.
  • Inspect cells E1:G1.

You should see the durable total plus the two rank headers beside naive_total_xp.

  • Select cell E2.
  • Calculate the durable total plus both ranks by pasting these formulas:
=SUMIFS(Events!$E$2:$E,Events!$B$2:$B,A2,Events!$C$2:$C,B2,Events!$D$2:$D,C2,Events!$F$2:$F,TRUE)	=1+COUNTIF($E$2:$E,">"&E2)	=1+COUNTIFS($B$2:$B,B2,$C$2:$C,C2,$E$2:$E,">"&E2)

How Do the Durable Ranks Work?

  • SUMIFS includes matching XP rows only when first_seen is TRUE.
  • COUNTIF counts every durable total above the current user's total.
  • COUNTIFS applies the same comparison inside the current cohort plus week.
  • Select cells E2:G2.
  • Extend the formulas through row 6 with the fill handle.
  • Inspect cells E3:G3 for user u-102.

The durable total is now 15. The cohort rank is 3.

Seeing 30 in the Durable Total?

Confirm the formula in column E includes Events!$F$2:$F,TRUE. That final condition excludes the repeated event.

Check that F5 on the Events tab shows FALSE. Help me debug the durable total and cohort rank formulas.

✔️ Awesome, I've got everything!

Your score table now separates the naive result from the deduplicated result.

ⓧ I'd like to double check the full code

user_id	cohort_id	week_id	naive_total_xp	durable_total_xp	global_rank	cohort_rank
u-101	cohort-A	2026-W42	=SUMIFS(Events!$E$2:$E,Events!$B$2:$B,A2,Events!$C$2:$C,B2,Events!$D$2:$D,C2)	=SUMIFS(Events!$E$2:$E,Events!$B$2:$B,A2,Events!$C$2:$C,B2,Events!$D$2:$D,C2,Events!$F$2:$F,TRUE)	=1+COUNTIF($E$2:$E,">"&E2)	=1+COUNTIFS($B$2:$B,B2,$C$2:$C,C2,$E$2:$E,">"&E2)
u-102	cohort-A	2026-W42	=SUMIFS(Events!$E$2:$E,Events!$B$2:$B,A3,Events!$C$2:$C,B3,Events!$D$2:$D,C3)	=SUMIFS(Events!$E$2:$E,Events!$B$2:$B,A3,Events!$C$2:$C,B3,Events!$D$2:$D,C3,Events!$F$2:$F,TRUE)	=1+COUNTIF($E$2:$E,">"&E3)	=1+COUNTIFS($B$2:$B,B3,$C$2:$C,C3,$E$2:$E,">"&E3)
u-103	cohort-A	2026-W42	=SUMIFS(Events!$E$2:$E,Events!$B$2:$B,A4,Events!$C$2:$C,B4,Events!$D$2:$D,C4)	=SUMIFS(Events!$E$2:$E,Events!$B$2:$B,A4,Events!$C$2:$C,B4,Events!$D$2:$D,C4,Events!$F$2:$F,TRUE)	=1+COUNTIF($E$2:$E,">"&E4)	=1+COUNTIFS($B$2:$B,B4,$C$2:$C,C4,$E$2:$E,">"&E4)
u-201	cohort-B	2026-W42	=SUMIFS(Events!$E$2:$E,Events!$B$2:$B,A5,Events!$C$2:$C,B5,Events!$D$2:$D,C5)	=SUMIFS(Events!$E$2:$E,Events!$B$2:$B,A5,Events!$C$2:$C,B5,Events!$D$2:$D,C5,Events!$F$2:$F,TRUE)	=1+COUNTIF($E$2:$E,">"&E5)	=1+COUNTIFS($B$2:$B,B5,$C$2:$C,C5,$E$2:$E,">"&E5)
u-202	cohort-B	2026-W42	=SUMIFS(Events!$E$2:$E,Events!$B$2:$B,A6,Events!$C$2:$C,B6,Events!$D$2:$D,C6)	=SUMIFS(Events!$E$2:$E,Events!$B$2:$B,A6,Events!$C$2:$C,B6,Events!$D$2:$D,C6,Events!$F$2:$F,TRUE)	=1+COUNTIF($E$2:$E,">"&E6)	=1+COUNTIFS($B$2:$B,B6,$C$2:$C,C6,$E$2:$E,">"&E6)
Build the cohort leaderboard

The read model needs a bounded view of one cohort for one week. Selectors make that partition boundary visible in the prototype.

  • Switch back to the Leaderboard tab.
  • Select cell A5.
  • Delete the existing global-ranking formula.
  • Select cell A1.
  • Build the cohort-and-week view by pasting this layout:
Cohort selector	cohort-A
Week selector	2026-W42

user_id	cohort_id	week_id	naive_total_xp	durable_total_xp	global_rank	cohort_rank
=IFNA(SORT(FILTER(Scores!A2:G,Scores!B2:B=$B$1,Scores!C2:C=$B$2),7,TRUE),"No scores for this week")

How Does the Cohort View Work?

  • FILTER keeps rows matching the selected cohort plus week.
  • SORT orders the filtered rows by cohort_rank from lowest to highest.
  • IFNA displays No scores for this week when the selected partition has no rows.
  • Inspect cells B1:B2.
  • Inspect the ranked rows beginning at A5.

You should see only cohort-A users from 2026-W42. User u-102 appears with durable XP 15 plus cohort rank 3.

Seeing Users from Both Cohorts?

Confirm B1 contains cohort-A. Check that the filter compares Scores!B2:B with $B$1.

Confirm B2 contains 2026-W42. Help me debug the cohort leaderboard formula.

✔️ Awesome, I've got everything!

Your spreadsheet now displays one bounded cohort leaderboard for the selected week.

ⓧ I'd like to double check the full code

A1: Cohort selector
B1: cohort-A
A2: Week selector
B2: 2026-W42
A4:G4: user_id | cohort_id | week_id | naive_total_xp | durable_total_xp | global_rank | cohort_rank
A5: =IFNA(SORT(FILTER(Scores!A2:G,Scores!B2:B=$B$1,Scores!C2:C=$B$2),7,TRUE),"No scores for this week")
Draw the production architecture

The spreadsheet proves the ranking logic. The draw.io diagram now turns that logic into separate write, read, recovery, plus dead-letter paths.

Why Cohort Keys Instead of Global Top-K?

Valkey Cluster assigns each key to one hash slot. A single global sorted set therefore concentrates its members plus requests on one shard.

A week-and-cohort key matches the product requirement. Each ranking view stays bounded to a small competition group.

The production write path uses Amazon SQS to absorb XP events. Amazon DynamoDB commits the event ID with the weekly total in one transaction.

  • Return to the architecture canvas from earlier.
  • Add one shape for each new label shown below.
  • Connect the shapes in the arrow order shown below.
Clients -> XP Event API -> Amazon SQS Standard Queue -> Score Processor -> DynamoDB TransactWriteItems
DynamoDB TransactWriteItems -> ProcessedEvents: PK event_id
DynamoDB TransactWriteItems -> WeeklyScores: PK week_id#cohort_id, SK user_id, total_xp

Why Put Both Records in One Transaction?

ProcessedEvents records whether the event ID has already been accepted. WeeklyScores holds the durable total for that user.

The transaction applies both writes as one unit. A rejected duplicate therefore cannot increase the weekly total.

  • Trace the write path from Clients to DynamoDB TransactWriteItems.
  • Inspect the two record branches below the transaction.

You should see one ingestion path ending in separate ProcessedEvents plus WeeklyScores records.

Write Path Hard to Follow?

Place the write path in one horizontal row. Put the two DynamoDB record shapes beneath the transaction shape.

Check that every connector points from left to right until the transaction branches. Help me arrange the leaderboard write path clearly.

The read side uses DynamoDB Streams to project durable totals into Valkey. Separate recovery paths keep that ranking view rebuildable.

  • Rename the existing Leaderboard API box to Leaderboard Read API.

The original placeholder now represents the API that serves cohort rankings.

  • Add the remaining read plus recovery shapes shown below.
  • Connect the shapes in the arrow order shown below.
WeeklyScores -> DynamoDB Streams -> Rank Projector -> Valkey Cluster
Clients -> Leaderboard Read API -> Valkey Cluster
Amazon SQS Standard Queue -> SQS Dead-Letter Queue
WeeklyScores -> Rebuild Job -> Valkey Cluster

What Do the Recovery Paths Protect?

  • The projector keeps the ranking view eventually consistent with durable scores.
  • The dead-letter queue isolates events that repeatedly fail processing.
  • The rebuild job recreates Valkey rankings from WeeklyScores after cache loss or a long projector outage.
  • Trace the read path from Clients to Valkey Cluster.
  • Trace the dead-letter path from Amazon SQS Standard Queue.
  • Trace the rebuild path from WeeklyScores.

You should see separate paths for serving ranks, isolating failed events, plus rebuilding the ranking view.

Recovery Arrows Crossing?

Place the read path above the write path. Put the dead-letter queue below Amazon SQS.

Place the rebuild job between WeeklyScores plus Valkey Cluster. Help me untangle the read and recovery paths.

  • Remove the old hot-key explanatory note from the canvas.
  • Replace the red callout text with Naive failure: one global key maps to one hash slot.

The red callout now states the single-key failure in one line.

  • Add the remaining architecture labels using this reference:
leaderboard:{week_id}:{cohort_id}
Partition key: week_id + cohort_id
Projection write: absolute total with ZADD
Read model: eventually consistent and rebuildable
Weekly reset: open a new week_id namespace
Long replay source: WeeklyScores, not the 24-hour stream

Why Use Absolute Scores?

ZADD updates a sorted-set member's score. Sending the durable total makes a replay write the same value again.

A relative increment could count a replay twice. The absolute projection keeps retries safe.

  • Place leaderboard:{week_id}:{cohort_id} beside Valkey Cluster.
  • Place each remaining label beside the path it explains.
  • Inspect the completed diagram for all four paths.

You should see distinct write, read, dead-letter, plus rebuild paths. The ranking key should visibly combine the week with the cohort.

Missing an Architecture Label?

Compare the callout text character by character with the reference block. Pay special attention to the braces in the Valkey key.

Confirm the projection label says absolute total with ZADD. Help me audit the architecture labels.

✔️ Awesome, I've got everything!

Your diagram now shows how events become durable scores plus rebuildable cohort rankings.

ⓧ I'd like to double check the full code

Clients -> XP Event API -> Amazon SQS Standard Queue -> Score Processor -> DynamoDB TransactWriteItems
DynamoDB TransactWriteItems -> ProcessedEvents: PK event_id
DynamoDB TransactWriteItems -> WeeklyScores: PK week_id#cohort_id, SK user_id, total_xp
WeeklyScores -> DynamoDB Streams -> Rank Projector -> Valkey Cluster
Clients -> Leaderboard Read API -> Valkey Cluster
Amazon SQS Standard Queue -> SQS Dead-Letter Queue
WeeklyScores -> Rebuild Job -> Valkey Cluster
leaderboard:{week_id}:{cohort_id}
Naive failure: one global key maps to one hash slot
Partition key: week_id + cohort_id
Projection write: absolute total with ZADD
Read model: eventually consistent and rebuildable
Weekly reset: open a new week_id namespace
Long replay source: WeeklyScores, not the 24-hour stream

Before the final check, do you expect the corrected score for u-102 to match the naive total or the unique event total?

  • Return to the Scores tab to inspect E3 plus G3.
  • Return to the architecture canvas to inspect the write, read, dead-letter, plus rebuild paths.

You should see 15 durable XP plus cohort rank 3 for u-102. Your diagram should show each recovery path feeding or protecting the rebuildable Valkey view.

That is the production core built. Duplicate delivery no longer inflates the durable score.

Next, you will test week rollover plus document how the design recovers from production failures.

Validate Recovery and Defend the Design

Your Google Sheets prototype now deduplicates retries. Its architecture also separates durable scores from the ranking view.

A production-ready design must explain what happens after duplicates, poison events, cache loss, projector downtime, or a week boundary. You will record those recovery rules before defending the design in 90 seconds.

In this step, get ready to:
  • Document the response to each failure scenario in the existing workbook.
  • Test that weekly rollover preserves the previous week's rankings.
  • Prepare a six-part system design defense that fits inside 90 seconds.
Document recovery behavior

A failure matrix connects each operational problem to an observable symptom. It also gives your design an explicit recovery rule.

  • Switch back to the Failure Lab tab in the existing workbook.
  • Select A1.
  • Fill A1:C8 by pasting this matrix:
Failure	Observable symptom	Design response
Duplicate SQS delivery	The same event_id arrives again	The ProcessedEvents conditional write rejects the duplicate, so WeeklyScores is not incremented twice
Poison event	A message repeatedly fails processing	Retries are bounded and the event moves to the SQS dead-letter queue for inspection and redrive
Valkey data loss	Leaderboard reads miss or return an incomplete cohort	Rebuild each week-and-cohort key from durable DynamoDB WeeklyScores records
Rank projector outage under 24 hours	Valkey rankings lag behind durable scores	Resume DynamoDB Streams consumption and apply absolute totals with ZADD
Rank projector outage beyond 24 hours	The stream can no longer supply the complete missed interval	Run the DynamoDB-to-Valkey rebuild job instead of treating the stream as a permanent event log
Stale leaderboard read	The user briefly sees an older rank	Return the available materialized view and expose a last-updated time in a production UI
Weekly rollover	The next week begins with no scores	Write to a new week_id namespace; keep the old week immutable for history and expire it later by policy

What Does This Matrix Prove?

  • An Amazon SQS retry remains safe because ProcessedEvents blocks the duplicate before the score changes.
  • A short projector outage can be recovered through DynamoDB Streams.
  • A longer outage uses durable DynamoDB WeeklyScores records as the replay source.
  • The Valkey ranking view remains rebuildable after cache loss.
  • Confirm the matrix fills A1:C8.
  • Check the row labeled Rank projector outage beyond 24 hours.

You should see the rebuild job named as the recovery path. The stream is not treated as a permanent event log.

Rows Split Into the Wrong Cells?

  • Undo the paste.
  • Select only A1.
  • Paste the complete matrix again.
  • Still stuck? Help me fix the Failure Lab paste..

Good work. Your architecture now has a specific response for every failure in the exercise.

✔️ Awesome, I've got everything!

Your Failure Lab tab now covers duplicate delivery, poison events, cache loss, projector outages, stale reads, and weekly rollover.

ⓧ I'd like to double check the full code

Failure	Observable symptom	Design response
Duplicate SQS delivery	The same event_id arrives again	The ProcessedEvents conditional write rejects the duplicate, so WeeklyScores is not incremented twice
Poison event	A message repeatedly fails processing	Retries are bounded and the event moves to the SQS dead-letter queue for inspection and redrive
Valkey data loss	Leaderboard reads miss or return an incomplete cohort	Rebuild each week-and-cohort key from durable DynamoDB WeeklyScores records
Rank projector outage under 24 hours	Valkey rankings lag behind durable scores	Resume DynamoDB Streams consumption and apply absolute totals with ZADD
Rank projector outage beyond 24 hours	The stream can no longer supply the complete missed interval	Run the DynamoDB-to-Valkey rebuild job instead of treating the stream as a permanent event log
Stale leaderboard read	The user briefly sees an older rank	Return the available materialized view and expose a last-updated time in a production UI
Weekly rollover	The next week begins with no scores	Write to a new week_id namespace; keep the old week immutable for history and expire it later by policy
Test weekly rollover

A new week begins with an empty materialized view while old scores remain stored. Changing the week selector tests this boundary without deleting existing data.

Before you change the selector, do you expect the current cohort to show rankings for a week with no events?

  • Switch back to the Leaderboard tab from earlier.
  • Select B2.
  • Replace 2026-W42 with 2026-W43.

You should see No scores for this week in the leaderboard area.

Still Seeing Ranked Users?

  • Confirm that 2026-W43 is entered in B2.
  • Confirm that the ranking formula remains in A5.
  • Still stuck? Help me debug the empty-week test..

The empty state proves that each week has its own ranking namespace. Your previous week remains available for history.

Before you restore the active week, do you expect the previous cohort rankings to return unchanged?

  • Restore the active week by entering 2026-W42 in B2.

You should see the cohort-A rankings return. User u-102 should have durable XP of 15 with cohort rank 3.

Rankings Did Not Return?

  • Check that B1 contains cohort-A.
  • Check that B2 contains 2026-W42.
  • Still stuck? Help me restore the cohort leaderboard..

That check confirms your weekly reset does not destroy historical rankings.

Prepare the 90-second defense

A concise system design defense follows the request from requirements through recovery. Six short sections keep the explanation grounded in evidence from your workbook.

  • Switch back to the empty Defense tab from earlier.
  • Select A1.
  • Add the six defense sections by pasting this text:
Requirements: 500 million registered users, with weekly rankings among small active-user cohorts rather than one universal contest.

Estimates: Under the supplied scenario, 10% DAU and 20 XP events per active user produce 1 billion events per day and about 138,889 events per second at a 12x peak. Eight leaderboard reads per DAU produce about 46,296 peak reads per second at a 10x peak.

Write path: The XP API places events on an Amazon SQS standard queue. A processor uses event_id as the idempotency key and atomically records the processed event with the user's durable weekly total in DynamoDB.

Read path and partitioning: A projector writes each absolute weekly total into a Valkey sorted set keyed by week and cohort. The read API queries only that small set. This avoids the single-key hot spot created by a 500-million-member global set.

Consistency and recovery: The ranking view is eventually consistent. Duplicate queue delivery is safe, poison events go to a dead-letter queue, and a lost or stale Valkey view is rebuilt from DynamoDB weekly totals. Weekly resets use a new week_id instead of clearing a live global structure.

Trade-off: The design gives up instantaneous global ranking in exchange for bounded keys, horizontal distribution, smaller failure domains, and a cache that can be reconstructed from durable data.

How Is the Defense Structured?

  • Requirements defines the product boundary as weekly cohort competition.
  • Estimates anchors the design in the calculated traffic peaks.
  • Write path explains how event-level idempotency protects durable totals.
  • Read path and partitioning explains how bounded cohort keys distribute ranking work.
  • Consistency and recovery covers retries, stale views, rebuilds, and weekly rollover.
  • Trade-off states what the design gives up to gain horizontal scale.
  • Confirm that the Defense tab contains all six labeled sections.
  • Confirm that the workbook has saved the completed Defense tab.

You should see sections labeled Requirements, Estimates, Write path, Read path and partitioning, Consistency and recovery, and Trade-off.

Defense Text Hard to Read?

  • Increase the width of column A.
  • Enable text wrapping for the populated cells.
  • Still stuck? Help me format the Defense tab..

✔️ Awesome, I've got everything!

Your defense now covers the requirements, estimates, data paths, partitioning, recovery model, and main trade-off.

ⓧ I'd like to double check the full code

Requirements: 500 million registered users, with weekly rankings among small active-user cohorts rather than one universal contest.

Estimates: Under the supplied scenario, 10% DAU and 20 XP events per active user produce 1 billion events per day and about 138,889 events per second at a 12x peak. Eight leaderboard reads per DAU produce about 46,296 peak reads per second at a 10x peak.

Write path: The XP API places events on an Amazon SQS standard queue. A processor uses event_id as the idempotency key and atomically records the processed event with the user's durable weekly total in DynamoDB.

Read path and partitioning: A projector writes each absolute weekly total into a Valkey sorted set keyed by week and cohort. The read API queries only that small set. This avoids the single-key hot spot created by a 500-million-member global set.

Consistency and recovery: The ranking view is eventually consistent. Duplicate queue delivery is safe, poison events go to a dead-letter queue, and a lost or stale Valkey view is rebuilt from DynamoDB weekly totals. Weekly resets use a new week_id instead of clearing a live global structure.

Trade-off: The design gives up instantaneous global ranking in exchange for bounded keys, horizontal distribution, smaller failure domains, and a cache that can be reconstructed from durable data.

The written defense becomes useful when you can deliver it as one connected explanation. The time limit forces each section to earn its place.

Before you start, do you expect the six sections to fit inside 90 seconds?

  • Start a 90-second timer on the device beside you.
  • Read the six Defense sections aloud from top to bottom.

You should finish the Trade-off section in 90 seconds or less.

Running Over 90 Seconds?

  • Pause briefly between sections instead of adding extra examples.
  • Read the estimates as rounded values.
  • Still running long? Help me rehearse this defense in 90 seconds..

Strong finish. Your 90-second defense now connects the working prototype to its recovery behavior.

Secret mission

Add a Second Region Without Double-Writing Scores

Your leaderboard can serve regional reads from two locations. Score writes need stricter control. Extend the diagram with a second regional read view and a fenced writer failover that rebuilds current rankings from durable scores.

Clean Up Your Resources

Clean Up Your Resources

Choose whether to keep your design artifacts, close them for later, or delete them entirely. This project deploys no cloud infrastructure, so your workbook plus diagram files create no ongoing charges.

Resources you used:

  • Google Sheets workbook named 500M Weekly Leaderboard.
  • draw.io source file named architecture.drawio.
  • Exported portfolio image of the completed architecture.

No Cloud Teardown Needed

Amazon SQS, Amazon DynamoDB, plus Valkey appear only in your architecture diagram. You have no deployed services to stop or delete.

Keep everything running

No action needed. Choose this if you want to keep refining the prototype or use it during system design interview practice.

  • Keep the 500M Weekly Leaderboard workbook in your Google Sheets account.
  • Keep architecture.drawio as the editable source for your architecture.
  • Keep the exported portfolio image for presentations or design reviews.

Pause - I'll come back to this later

Close the browser tabs while retaining every artifact. Your prototype remains ready for another design session.

  • Use the File menu in the draw.io tab from earlier to save the latest changes to architecture.drawio.
  • Close the draw.io browser tab.
  • Close the Google Sheets browser tab.

Your workbook remains in your account. Your saved diagram files remain on your device.

Delete - I don't want to use this again

Remove all project artifacts to start fresh. Deletion becomes final for local files after you empty the Trash or Recycle Bin.

  • Move 500M Weekly Leaderboard to trash from your Google Sheets file list.
  • Return to the folder containing architecture.drawio in Finder on macOS or File Explorer on Windows.
  • Move architecture.drawio to the Trash on macOS or Recycle Bin on Windows.
  • Locate the exported portfolio image in Finder on macOS or File Explorer on Windows.
  • Move the exported portfolio image to the Trash on macOS or Recycle Bin on Windows.
  • Open the Trash on macOS or Recycle Bin on Windows.
  • Empty the Trash on macOS or Recycle Bin on Windows.

You should no longer see 500M Weekly Leaderboard among your active Google Sheets files. Your saved folder should no longer contain architecture.drawio or the exported portfolio image.

Nice Work!

Nice Work!

Excellent work! Your working Google Sheets prototype now anchors a defensible draw.io architecture for 500 million registered users.

You've learned how to:

  • Build a weekly cohort leaderboard with reliable event deduplication. Its durable totals produce cohort-specific ranks.
  • Create a capacity model for 500 million registered users. It quantifies peak XP traffic. It calculates leaderboard reads. It exposes cohort counts plus decimal storage estimates. It also reveals the hot-key risk in one global Valkey sorted set.
  • Design an event-driven architecture that moves XP events through Amazon SQS into Amazon DynamoDB. Project absolute totals through DynamoDB Streams into partitioned ranking views. Defend dead-letter handling. Explain replay from durable scores. Describe eventual consistency. Present the full design in a concise interview walkthrough.
  • Complete the optional Secret Mission by adding regional read views backed by one active score writer. Fence the old Region A writer before Region B accepts XP events. Rebuild the Region B cohort view from durable WeeklyScores records before serving current rankings.

Ready to quiz yourself?