Interview Question Bank · PERCENT_RANK · ties
7. Power users: the top 10%
MediumSaaSQuestion #115
Concept: PERCENT_RANK · tiesshow
PERCENT_RANK() OVER (ORDER BY x DESC) gives each row the share of other rows ranked strictly ahead of it: (rank - 1) / (rows - 1). Tied rows share a rank, so they get the same percent rank and land on the same side of any cutoff. NTILE and ROW_NUMBER, by contrast, cut groups of equal size and will split a tie arbitrarily.
Example (different data):
SELECT seller_id, revenue, PERCENT_RANK() OVER (ORDER BY revenue DESC) AS pr FROM seller_totals ORDER BY revenue DESC;
Your task
Tallyboard is inviting its most active users to a beta. A power user is someone for whom at most 10% of the other users had more events in the last 30 days, so users tied on events are treated the same.
From saas_user_activity, return user_id, account_id, events_30d and pct_rank (the share of other users with more events, rounded to 3 decimals) for every power user. Sort by events_30d descending, then user_id.
Starting Postgres in your browser…
Checking your access…