Everyone thinks App Store rankings are a SELECT app_id, COUNT(*) FROM downloads GROUP BY app_id ORDER BY count DESC LIMIT 100. Count the downloads, sort, take the top 100, done. Then you look at a real store and the assumption falls apart in three separate ways at once. First, “trending” is not the same as “most downloaded” - a five-year-old app with a huge install base racks up more downloads per minute than a brand-new hit, yet the new hit is what is trending, so trending has to measure velocity, not volume. Second, there is not one chart, there is a chart per country times per category times per window (top free, top paid, top grossing), which is tens of thousands of distinct top-K lists that all have to stay fresh. Third, the windows are not all the same shape - “top 100 trending every minute” is a fast rolling score, “top 1000 daily” and “weekly” are rolling windows that must expire old events, and “top 1000 all-time” is a monotonic cumulative counter that must never forget anything. One naive GROUP BY cannot be all three.
...