Design a Sales Ranking by Category

TL;DR. Do not calculate rankings when a page loads. Record every order as an event, recompute the top products hourly with a batch job, and serve the finished lists from a store built for reads. Readers never touch the raw data, and a failed job just means rankings stay an hour old.

Learning Objectives

• Separate the path that computes rankings from the path that serves them
• Estimate storage and traffic from the requirements
• Design a batch job with a rolling time window
• Swap in new results without readers seeing a half-finished list

Intuition

A music chart is not worked out each time someone looks at it. Sales are collected, the chart is compiled once in a while, and the finished chart is printed for anyone to read. Lots of readers, few compilers, and nobody minds that it was compiled an hour ago.

Requirements

Functional

• Calculate the best-selling products by category over the past 7 days.
• Show a category’s ranking to users.

Non-functional

• High availability for reads.
• Rankings refresh hourly.
• Products can belong to more than one category. There are no subcategories.

Assumptions: 10 million products, 1,000 categories, 1 billion orders a month, 100 billion ranking reads a month.

Capacity Estimation

A month has about 2.5 million seconds, which keeps the arithmetic simple.

A 100:1 read to write ratio, plus data that is an hour stale by design, points strongly to precomputing and caching.

API and Data Model

GET /api/v1/categories/{category_id}/top-products?limit=100

{
  "category_id": 1234,
  "computed_at": "2026-10-31T10:00:00Z",
  "products": [{ "rank": 1, "product_id": 42, "units_sold": 18230 }]
}

CREATE TABLE sales_rank (
  run_id      BIGINT  NOT NULL,
  category_id INT     NOT NULL,
  rank        INT     NOT NULL,
  product_id  BIGINT  NOT NULL,
  units_sold  BIGINT  NOT NULL,
  PRIMARY KEY (run_id, category_id, rank)
);

Returning computed_at lets the page be honest about how fresh the list is.

High-Level Architecture

flowchart LR
    C["Checkout"] --> L["Order events"]
    L --> O["Object storage"]
    O --> J["Hourly ranking job"]
    J --> S["Ranking store"]
    U["Users"] --> A["Read API"]
    A --> M["Memory cache"]
    M --> S

Deep Dives

The ranking job

The job scans the last 7 days of events and works in three steps:

1. Join and expand. Attach each product’s categories, so one order line produces one row per category.
2. Sum. Group by (category_id, product_id) and add up the quantities.
3. Rank. Within each category, keep the top N.

ranked = (
    orders.filter(col("created_at") >= now - timedelta(days=7))
    .join(product_categories, "product_id")
    .groupBy("category_id", "product_id")
    .agg(sum("quantity").alias("units_sold"))
    .withColumn("rank", row_number().over(
        Window.partitionBy("category_id").orderBy(desc("units_sold"), "product_id")))
    .filter(col("rank") <= 100)
)

The product_id tie-break keeps the order stable between runs, so tied products do not swap places for no reason.

Rolling windows without rescanning everything

Reading 7 days of data every hour repeats a lot of work. Instead, summarize each finished hour once into (hour, category, product, units). Each run then adds up 168 hourly summaries, and old ones simply drop out of the window.

Publishing safely

A reader should never see a half-written ranking:

sequenceDiagram
    participant J as Ranking job
    participant S as Ranking store
    participant A as Read API
    J->>S: Write all rows under a new run_id
    J->>J: Check row counts and totals
    J->>S: Point current_run_id at the new run
    A->>S: Read rows for current_run_id

Checks before the switch catch bad runs. If a run has far fewer rows than the last one, keep serving the old results and raise an alert.

Serving reads

40,000 reads per second across just 1,000 lists is an ideal caching case, with traffic concentrated on the popular categories. Put a memory cache in front of the store, with a key that includes the run, such as rank:{run_id}:{category_id}. Publishing a new run then needs no invalidation. Requests just start using the new keys, and old ones expire. A small jitter on TTLs and a warm-up of top categories prevent a stampede when the switch happens.

Trade-offs

If hot items need fresher rankings, run a streaming job for only the busiest categories alongside the batch job.

Scaling and Failure Modes

Common Pitfalls

Querying the orders database for rankings. A week-long aggregation on the transactional database harms checkout. Keep analytics separate.

Overwriting results in place. Readers see a mix of old and new. Write a new version, then switch to it.

Counting cancelled orders. Rankings get inflated, or can be gamed. Decide how returns and fraud are handled, and record those events too.

Exercise

Product managers want a “trending” list that favours products selling faster than last week. What changes?

Key Takeaways

• Separate computing from serving when reads vastly outnumber writes and slight staleness is acceptable.
• Keep raw events in cheap storage, so results can be recomputed.
• Roll up finished hours so that a rolling window does not rescan everything.
• Publish by switching to a complete new version, which also makes rollback easy.
• Including the version in cache keys removes the need to invalidate.

Further Reading

• Stream vs batch
• Design a leaderboard covers rankings that must update instantly.
• OLTP vs OLAP

Flashcards

References

• Donne Martin, “Design Amazon’s sales rank by category feature,” The System Design Primer. https://github.com/donnemartin/system-design-primer/tree/master/solutions/system_design/sales_rank