Drill 7 of 12 in SaaS questions
0

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…