Design Sales Ranking by Category

The ask

Sales ranking by category orders the products inside one category by how well they sell. Each category gets its own list, from the strongest seller down.

The store's orders feed the ranking. A product rises when buyers keep buying it, not when a seller promotes it.

Ranking runs on a schedule, not per request. Readers need a fast prepared list, and the store can accept a list that lags a little.

The measure must resist gaming. Cancelled and refunded orders are not sales, and the ranking must treat them that way.

Estimate the scale

The store's orders run at 10,000,000 a day. Every order names the products bought and their categories.

The catalogue is divided into 10,000 categories. Categories range from huge departments to narrow shelves.

Ranking is recomputed every hour. A day therefore holds 24 recomputes.

One recompute reads the hour's orders when it maintains hourly counts. The hourly slice is far smaller than the full day.

10,000,000 orders / 24 hours = about 416,667 orders in one hour's slice.

The stored result stays small by design. Only the leading products of each category are kept as ranked rows.

API and data

Get ranking takes a category and a page number. It returns the stored ranked products for that category, best first.

An order record holds the product, the category, the quantity, and the order state. Paid, cancelled, and refunded states are kept distinct.

A sales counter holds one product's kept sales inside the ranking window. The window is the last 30 days, a stated choice of the design.

A ranked row holds the category, the product, its place, and its window sales. Readers fetch ranked rows and nothing else.

The ranking stores the top 100 products per category as the design choice. Deeper places are computed on request from the counters, rarely and slowly.

10,000 categories x 100 ranked rows = 1,000,000 stored ranked rows.

High-level design

Orders land in an append-only order log. The ranking workers read the log and maintain sales counters per product.

Counters are grouped by category. One category's counters live together, so one category can be ranked alone.

On average the catalogue spreads evenly across categories. The average hides wide differences, and the design plans per category, not from the average.

100,000,000 products / 10,000 categories = about 10,000 products per category on average.

Every hour, a ranking job sorts each category's counters and writes the new top rows. The write swaps the whole list at once, so readers never see a half-built list.

Category pages read the stored rows through a cache. A ranking refresh replaces the cache entry with the new list.

Deep dive: the sliding window

The ranking window is the last 30 days of kept sales. A sliding window moves with time, dropping old days as new days arrive.

Daily buckets make the slide cheap. Each product keeps one sales count per day, and the window sum adds the daily counts it covers.

When a day leaves the window, its bucket is subtracted once. No recompute ever rescans a full month of orders.

Refunds arrive later than the sale they cancel. A refund subtracts from the day of the original sale, so an old bucket can still change.

A product sold hard and refunded hard therefore falls at the next recompute. The fall is automatic, with no manual cleanup.

Seasonal products rise and fade through the same slide. A winter product's strong December leaves the window during January, day by day.

Gaming and trust

A seller can try to buy a higher place with fake orders. The store's order checks reject unpaid orders before they reach the ranking.

Orders cancelled before payment never count. Orders refunded after payment stop counting at the next recompute.

Bulk orders from one buyer count with a cap as a design rule. One warehouse buyer should not crown a consumer bestseller.

The ranking also creates a feedback loop. A high place brings views, views bring sales, and sales hold the place.

New products fight that loop with a fair start. Products with few sales are ranked on their window sales alone, and the loop is named openly rather than hidden.

When parts fail

A failed recompute leaves the previous list in place. Readers see ranks an hour older, and the next recompute heals the gap.

A late order log delays counters, never the store. Orders keep landing, and the ranking catches up from the log position.

A corrupt category list is rebuilt from that category's counters. Categories rank independently, so one rebuild never blocks the others.

Evaluation and trade-offs

Hourly recomputes trade freshness for a calm, provable build. Each list comes from a complete pass over the counters, never a partial update.

A 30-day window trades memory of old glory for current truth. Products must keep selling to keep their places.

Storing only the top rows trades depth for speed. Buyers browse the head of each list, and the head is what the store guarantees.

The design fits because ranking is a batch shape over honest inputs. Paid orders minus refunds, counted per category, refreshed every hour.

Scale of the ranking