Design Sales Ranking by Category

This is lesson 9 of System Design Case Studies, Part 2. Lesson 5 (trending topics and top-K) ranked items in a huge stream and accepted approximate counts. This case is the calmer sibling: the data is smaller and exact, because a sales number is money and units, and a seller will check it. Lesson 10 (Mint-style data pipeline) covers the event pipeline that feeds a system like this, and Lesson 11 (scaling on AWS) covers where such a service would run. The problem comes from the system-design-primer (folder solutions/system_design/sales_rank).

Words used in this lesson

• Sale event is one record: time, product, category, quantity, price. We only need a few fields to rank.
• Window is the time range we count in. "Last 7 days" is a sliding window: it moves forward with time.
• Bucket is a small slice of time (here one hour) with its own counts. A window is the sum of its buckets. When a bucket falls out of the window we subtract it.
• Aggregation means combining many records into one number, here "units sold per product per category".
• Sorted set (Redis ZSET) is a collection of unique members, each with a numeric score, kept ordered by score. We use the product as the member and units sold as the score.
• ZINCRBY adds to a member's score. ZREVRANGE reads members from the highest score down. ZREVRANK returns a member's position from the top (0 is first).
• Top-N is the N best items, for example the top 100.
• Write path / read path are the two halves of the system: how data gets in, and how it gets out.
• Hot category is a category that gets a large share of all traffic (for example "Electronics" on a sale day).
• Backfill means rebuilding results from the raw history, for example after a bug or a new ranking rule.
• Idempotent means doing the same operation twice has the same effect as once. We need it because event streams deliver some events twice.
• Late event is an event that arrives after its time slice has already been counted.

1. Requirements

Functional

• A user opens a category page and sees the top N products by units sold (or revenue) for a window: last day, last 7 days, last 30 days.
• A product page can show "rank #12 in Toys". That is a rank lookup.
• A product may belong to more than one category. A product does not change category (the primer's assumption).
• Results are refreshed about every hour at least; popular categories could be fresher.

Non-functional

• Read heavy: the primer assumes 100 reads for each write.
• Fast reads: single-digit milliseconds, from a cache or an in-memory store.
• Highly available reads: a slightly old list is better than an error page.
• Accurate: counts are exact (no sampling), and a sale is never counted twice.
• Cheap: do not recompute the whole world every time a sale arrives.

Out of scope here: the rest of the shop (cart, payments, search), personalised ranking, and sub-categories (the primer also skips them).

2. Back-of-envelope numbers

The first block uses the primer's assumptions. The second block is mine; every number is an assumption.

Products                    10 millionCategories                  1,000Sales transactions          1 billion / month  = 1e9 / 2.5e6 s = ~400 sales/s on averageReads                       100 billion / month = ~40,000 reads/s on average (100:1)Sale record                 ~40 bytes -> 40 GB per month of raw sales, ~1.4 TB in 3 yearsMy assumptionsPeak                        5x average: ~2,000 sales/s, ~200,000 reads/sEach product in             ~2 categories on average -> ~4,000 category-updates/s at peakWindow                      7 days = 168 hourly bucketsSorted-set size             10M products x 2 categories = 20M members per windowRedis memory per member     ~64 bytes (guess; check with MEMORY USAGE) -> 20M x 64 B = ~1.3 GB per window3 windows (1d, 7d, 30d)     ~4 GB total; fits in one big node or a few small ones

What this shows:

• Writes are small. 400 sales per second is a modest stream. Even 2,000 per second is easy for one stream partition.
• Reads are the load. 40,000 to 200,000 reads per second means the read path must be a lookup in memory, not a query on sales data.
• The ranked state fits in memory. A few GB for all the windows. We do not need an expensive big-data job just to hold the answer.
• A list of the top 100 per category is tiny: 1,000 categories x 100 entries x ~50 bytes = 5 MB. We can cache that on every API server.

3. API

GET /v1/categories/{categoryId}/top?window=7d&limit=50&offset=0-> 200 OK{ "category": "toys", "window": "7d", "asOf": "2026-10-11T10:00:00Z",  "items": [ { "rank": 1, "productId": "p0", "units": 3884 },             { "rank": 2, "productId": "p15", "units": 498 }, ... ] }GET /v1/products/{productId}/rank?category=toys&window=7d-> 200 OK   { "category": "toys", "window": "7d", "rank": 12, "units": 211 }# Internal: the sales service publishes events (not HTTP, usually a stream){ "id": "ord-91-line-2", "ts": 1760000000, "productId": "p15", "categories": ["toys", "gifts"], "qty": 2 }

Notes:

• asOf tells the reader how fresh the list is. Honest and cheap.
• The event has a unique id (an order line id). It is what makes counting idempotent (section 6.5).
• Pagination: top 50 comes from one ZREVRANGE. Deep pages (rank 5,000) are rare; cap the depth.

4. Data model

Raw sales (object store, e.g. Parquet files by day, or a table partitioned by day)    event id | ts | product id | category ids | qty | price | ...        <- the source of truth, kept for backfillStream (Kafka-style)   topic "sales", partitioned by product id        <- same product -> same partitionAggregator state (memory + checkpoint)    hourly buckets:  hour -> category -> product -> units               <- so an hour can be subtracted later    seen event ids per hour                                              <- dedupeServing store (Redis)    key  rank:{category}:{window}     type ZSET   member = product id   score = units in the window    optional  top:{category}:{window} type STRING a JSON blob of the top 100 (for a CDN or app cache)Product catalogue (SQL)   product id -> name, image, price     <- joined by the API when it builds the page

Why the sorted set is the right structure: one ZREVRANGE rank:toys:7d 0 49 gives the top 50, one ZREVRANK gives a product's position, and ZINCRBY updates a score and moves the member in O(log n). A SQL GROUP BY plus ORDER BY would have to scan and sort.

5. High-level architecture

 Sales service ---> Stream "sales" (partition by product id) ---> Aggregator workers (stateful) (orders)                  |                                        1. dedupe by event id                           |                                        2. add to hourly bucket (hour, category, product)                           v                                        3. every few seconds: apply the batch as ZINCRBY                  Raw sales archive                                 4. every hour: subtract buckets that left the window                  (object store, Parquet)                                         |                           |                                                      v                           |                                          Redis ZSET  rank:{category}:{window}                           v                                                      |                  Backfill / batch job  ------ rebuilds the ZSETs --------------->+                  (replay raw sales)                                              |                                                                                  v Users ---> CDN / API cache (5-60 s) ---> Rank API (reads ZREVRANGE / ZREVRANK) --+                                              + product details from catalogue

Two paths, kept apart on purpose:

• Write path: events flow in one direction through the stream and the aggregator. Nothing here is on a user's page load.
• Read path: the API reads a ready-made sorted set. The cost of a read does not depend on how many sales happened.

6. Deep dives

6.1 Two ways to build the ranking: batch and incremental

The primer uses MapReduce: step 1 maps each log line to (category, product) -> quantity and reduces by sum, step 2 sorts, and the output goes into a sales_rank SQL table that a Rank API reads. That is a good first answer and fits the "update hourly" requirement.

In an interview I would say: start with the hourly batch, because it is simple and the requirement allows it. Then say the incremental path as the improvement for freshness and for cost, and keep the batch job anyway as the backfill and as the checker. This lesson's code builds the incremental version and uses the batch version to prove it right.

6.2 The sorted set

I wrote a small sorted set (sortedset.ts) with the same behaviour as the Redis commands we need: order by score, ties broken by member, ZREVRANGE walks from the top. Real Redis uses a skip list plus a hash table. Mine uses a sorted array plus a map, which is simpler and fine to show the idea (insert is O(n) in the array, where Redis is O(log n)).

export class SortedSet {  private score = new Map<string, number>();  private arr: Array<[number, string]> = [];            // ascending by (score, member)  zincrby(member: string, delta: number): number {    const next = (this.score.get(member) ?? 0) + delta;    if (next <= 0) { this.zrem(member); return 0; }       // sales never stay at zero or below    this.zadd(member, next);    return next;  }  zrevrange(start: number, stop: number)  // highest score first  zrevrank(member: string)                // position from the top, 0 = best}

Test 1 checks the ordering rules: after increments the order is b 9, a 6, c 5, zrevrank("c") is 2, a member whose score returns to zero is removed, and two members with the same score come out in reverse member order (Redis returns equal scores in lexical order, so ZREVRANGE gives the reverse). Ties matter in a ranking page: decide a rule (here by member id) so that the list does not shuffle between refreshes.

6.3 The write path: windowed aggregation

The aggregator (ranker.ts) keeps two things:

1. Hourly buckets: hour -> category -> product -> units. These are the pieces of the window.
2. One sorted set per category holding the sum over the buckets still in the window.

// A batch of sales arrivesfor (const s of sales) {  const h = Math.floor(s.ts / 3600);  if (h > this.nowHour) { flush(); this.advance(h); }     // time moves forward with the data  if (!this.inWindow(h)) { this.dropped.tooOld++; continue; }  if (seenInThisHour.has(s.id)) { this.dropped.duplicate++; continue; }  for (const c of s.categories) { bucket[h][c][s.product] += s.qty; delta[c][s.product] += s.qty; }}flush();    // ONE zincrby per (category, product) in this batch// When the clock moves to hour H, every bucket at or before H - 168 leaves the window:for each expired bucket: zincrby(product, -units) for each (category, product); delete the bucket

The sliding window is therefore "add the new, subtract the expired". It is the same idea as the ring of buckets in Lesson 5, with exact counters instead of sketches. A sale is touched twice in its life: once when it arrives and once when its hour leaves the window.

A bug I hit, and what it taught me. My first version of applyBatch collected the changes for the whole batch and wrote them to the sorted sets at the end. When one batch contained events that moved the clock forward by many hours, the old buckets were subtracted before the changes from those old hours were written. The sorted sets then ended up with sales that should have expired. Test 7 (the backfill test) failed. The fix is the flush() call right before advance(): apply pending changes first, then expire old hours. The rule: in a windowed aggregator, the order of "add" and "expire" matters, and a test against a plain recompute finds this class of bug fast.

6.4 Is the incremental result correct?

Never trust incremental state without a check. batchTop() is the simple version: scan all raw events, keep the ones inside the window and in the category, sum, sort. I ran this demo (demo2.ts): 200,000 sales over 14 days, 50 categories (40% of sales in one hot category, cat0), 20,000 products with a skewed popularity, 20% of sales in two categories, a 7-day window, applied in batches of 1,000.

sales: 200000 over 14 days; window 7 days; now hour 335 dropped {"tooOld":0,"duplicate":0}cat0 (hot) top 5: [["p0",3884],["p15",498],["p17",460],["p1",457],["p12",405]]cat7 top 3     : [["p0",184],["p9439",138],["p8541",138]]all 50 categories: incremental top-10 equals batch recompute? trueZSET members: cat0 778 | median category 34 | total 2427

All 50 top-10 lists matched the recompute exactly (same products, same units, same order). p0 appears in both lists because the data has many sales for product p0 and it sits in several categories. Note cat7's second and third place both have 138 units: a tie, resolved by member order, the same on both sides.

The tests (ranker.test.ts, run with node --test ranker.test.ts):

ok 1 - sorted set: zincrby, zrevrange, zrevrank, ties by memberok 2 - top-N per category, a product can be in two categoriesok 3 - window slides: old hours are subtractedok 4 - duplicates are ignored, sales older than the window are droppedok 5 - a late sale that is still inside the window is countedok 6 - incremental ranker equals the batch recompute on random dataok 7 - backfill: rebuilding from raw events gives the same top-N as the live ranker# tests 7# pass 7# fail 0

6.5 Duplicates, late events and exactness

Streams usually give at-least-once delivery: after a crash or a retry an event can arrive twice. If every event is added to a counter twice, the rank is wrong and nobody notices. So the aggregator keeps the set of event ids it has seen per hour bucket and drops repeats (test 4). When an hour leaves the window its id set is deleted too, so memory stays bounded. For very high volume you can keep the ids in a shared store with an expiry, or let the stream give exactly-once effects through transactions (more complex).

Late events. A sale can arrive hours after it happened (a phone was offline, a queue was backed up). Because we bucket by event time, a late sale simply goes into its old hour's bucket and is added to the sorted set (test 5, 10 hours late). It will also be subtracted when that hour expires, which is correct. A sale older than the whole window is dropped and counted (dropped.tooOld) so you can see how often it happens. If the rank must include very late sales, that is a case for the backfill path.

Returns and cancellations. A refund is a negative sale event. It can use the same path (a negative qty). My code removes a member whose score drops to zero; a real design must also decide whether a return after the window counts. I did not implement this.

6.6 Freshness versus cost

How often should the sorted set change? Every sale, or every few seconds? Each ZINCRBY is a write to Redis. Coalescing, adding up the sales for the same (category, product) in a short batch and writing once, cuts the writes. I measured it two ways.

The ordinary mixed workload above (200,000 sales over 14 days) counting how many sorted-set writes (including the expiry subtractions) each batch size caused:

batch size     1: ZSET writes 352660batch size   100: ZSET writes 349861batch size  5000: ZSET writes 344434

Barely any saving: 2.3% at the largest batch. In this data the sales in one batch mostly belong to different products, so there is little to add together. Honest result: batching does not always help.

A flash sale is different: 100,000 sales in one hour, all in one category, spread over 20 products (flash.ts):

batch     1 sales: ZSET writes 100000batch  1000 sales: ZSET writes 2000batch 10000 sales: ZSET writes 200

Here batching cuts writes by 50 times with batches of 1,000 and by 500 times with batches of 10,000, because the same 20 products repeat in every batch. The cost of waiting is freshness: a batch window of a few seconds means the list is a few seconds old.

What freshness do you need? The primer says hourly is acceptable. A useful way to think about it:

Nobody sees a difference between "updated 3 seconds ago" and "updated 30 seconds ago" on a best-sellers page. Spend freshness only where users notice it (a flash sale page).

6.7 Hot categories

Traffic is not even (a primer assumption). In my data, cat0 gets 40% of sales. Three different things get hot, and each has a different fix:

My numbers show the shape of it: for the hot category the sorted set had only 778 members while the median category had 34, but the hot category got a large share of the writes. Size is not the issue; update rate is. Redis can do very many simple operations per second on one core, but one key lives on one node (in a cluster), so one key cannot be spread without splitting it yourself.

Reading cost, for scale: in my process, a top-10 read from the in-memory sorted set took about 0.25 microseconds. That is a local array slice and says nothing about the network, serialisation or a real Redis. The point is only that the work is a lookup, not a computation.

6.8 The read path and capacity

• The Rank API calls ZREVRANGE rank:{category}:{window} 0 N-1 and joins product names and images from the catalogue (cache these too).
• Top-100 per (category, window) as one JSON string in Redis or in an API process cache. 1,000 categories x 3 windows x ~5 KB = ~15 MB. Everything that is read a lot fits in memory on every API server. (Assumed sizes.)
• For rank of one product, ZREVRANK is O(log n). For products that are not in the sorted set, the answer is "unranked", not an error.
• Reads must survive a Redis failure: keep the last top-100 blobs in the API process or a second store, and serve them with the asOf time. A stale list is better than a 500.

Memory trimming. Do we need the whole sorted set of 20 million members, or only the top 1,000 per category? Keeping all is simple and lets you answer "rank of any product" and lets expiry subtract exactly. Keeping only the top K saves memory, but then a product just below the cut cannot move up when the products above it expire (you do not know its score). The usual compromise is: keep the full set for the exact window, or keep a bounded list and rebuild it from buckets every so often. I kept the full sets; I did not test the bounded variant.

6.9 Backfill and fixing mistakes

Sooner or later you must rebuild: a bug in the aggregator, a new rule (exclude returned items), a new category tree, or a lost Redis. This is why the raw sales are kept in cheap storage and why the batch job exists.

Backfill procedure1. Replay raw sales for the window into a NEW sorted set key (rank:cat:7d:v2). Never write over the live key.2. When it has caught up, compare it with the live one (sample categories, check top-N equality).3. Switch the API to the new key (a version number in the key name or in a config value).4. Delete the old key after a safe period.

Test 7 does the small version: it builds a ranker from a stream in small batches, builds a second one by applying all events in one go, and checks that the top-10 is identical. In the demo:

batch recompute for ONE category: 21.5 msbackfill all 200k raw events into a fresh ranker: 762 ms; same cat0 top-10? true

200,000 raw sales rebuilt every category's sorted set in 0.76 seconds in one process. The real archive is 1 billion sales a month, about 5,000 times larger than my demo; I would scale the backfill by partitioning the replay by product and merging, which works because a product's total is a sum. (This is arithmetic, not a measurement.)

6.10 Rank queries and products in more than one category

A product in two categories simply gets an update in each category's sorted set (my events carry a list of categories, and test 2 covers it). If categories can change, or a product is added to a category later, you must rebuild that product's contribution from the raw data. The primer says items do not change categories, which avoids this. I would ask the interviewer before assuming it.

If you need an "all products" ranking, either keep one more sorted set (rank:all:7d) updated for every sale, or compute it on a schedule. It is one more write per sale. Do not add it for free; it is a hot key.

7. Failure modes and trade-offs

Trade-offs to say aloud

• Batch vs stream: batch is simple and exact, and the requirement allows it. Stream gives freshness and lower cost per update, at the price of state and care.
• Freshness vs write cost: coalescing helps a lot on skewed bursts (500x in my flash-sale test) and very little on spread-out data (2.3%).
• Full sorted set vs top-K only: full is exact and supports any rank lookup; top-K saves memory but cannot recover when the top items expire.
• Units vs revenue vs a mix: the score is the product's business decision. Units are simple to add and subtract; a weighted score is also possible if it is a sum over events.
• Exact vs approximate: here exact, because the data is small enough. If the item count grew to billions, Lesson 5's sketches would come back.

8. How to say it in an interview (60 seconds)

"Sales are written about 400 times a second and read about 100 times more, so I separate the paths. Sales events go to a stream partitioned by product, and raw events are archived for backfill. A stateful aggregator keeps hourly buckets per category and product, dedupes by event id, and applies micro-batches to a Redis sorted set per category and window, where the member is the product and the score is units sold. When an hour leaves the 7-day window I subtract its bucket. Reads are one ZREVRANGE for the top N or ZREVRANK for one product, behind an API and CDN cache of a few seconds, so the cost of a read does not depend on the number of sales. The simplest version is the primer's hourly batch job, and I keep it as the checker: in my test the incremental result matched a full recompute for all 50 categories. Coalescing writes helped 500 times in a flash-sale test and 2% on spread-out data. A hot category I handle with micro-batches, a cached top-100, and key sharding with an exact merge. Backfill replays raw sales into a new versioned key, then switches over."

Run it yourself

Files: sortedset.ts, ranker.ts, ranker.test.ts, demo2.ts (the 200,000-sale run) and flash.ts (the flash-sale run). Put them in one folder, then:

node --test ranker.test.tsnode demo2.tsnode flash.ts

Node 22.22 strips the types, so no build step is needed. The code uses only Node's standard library.

Interview questions

Key takeaways

• Split the write path (stream, dedupe, hourly buckets, windowed aggregation) from the read path (a precomputed sorted set per category and window, served behind a cache).
• Start with the primer's hourly batch. Add the stream version for freshness and cost, and keep the batch as backfill and as the correctness check.
• Sliding window = add new events, subtract the expired hour. Order matters: apply pending changes before expiring. I hit this bug and a test against a recompute caught it.
• Measured: the incremental result matched a full recompute for all 50 categories; a full rebuild of 200,000 sales took 0.76 s locally.
• Coalescing writes: 100,000 writes became 200 (flash sale, batches of 10,000) but only 2.3% fewer on spread-out data. Measure on real traffic.
• Dedupe by event id and bucket by event time so duplicates and late events do not break the counts.
• A hot category is a write-rate problem more than a size problem: micro-batch, shard the key and merge exactly, cache the top-N.
• Keep raw sales so you can rebuild into a new versioned key and switch over.

What's next

Lesson 10: Design a Mint-Style Data Pipeline. The sales events in this lesson came from "somewhere". That lesson shows how to pull data from many outside sources, clean it, categorise it, and keep it correct as it flows through stages.