Interview Question Bank · ROW_NUMBER · COUNT(*) OVER () · CEIL · Pareto share
12. Revenue share of the top 20% of customers
HardE-commerceQuestion #96
Concept: ROW_NUMBER · COUNT(*) OVER () · CEIL · Pareto shareshow
A Pareto check asks what share of revenue the top X% of customers bring in. Rank customers by revenue with ROW_NUMBER (a tie-breaker makes the cut exact), put the customer count on every row with COUNT(*) OVER (), and keep rows with rn <= CEIL(0.2 * count). RANK and PERCENT_RANK let ties stretch or shrink the group, and the count must be of customers who actually bought.
Example (different data):
WITH r AS (
SELECT seller_id, gmv,
ROW_NUMBER() OVER (ORDER BY gmv DESC, seller_id) AS rn,
COUNT(*) OVER () AS sellers
FROM seller_gmv
)
SELECT SUM(gmv) FILTER (WHERE rn <= CEIL(0.1 * sellers)) AS top_10pct_gmv
FROM r;Your task
Quillmere's CEO suspects a handful of big spenders carry the business and asks what share of all-time revenue comes from the top 20% of customers.
Using completed orders (revenue = quantity * unit_price, including lines whose product was removed), rank customers who have at least one completed order by revenue, highest first, ties broken by lower customer_id. The top 20% are the first CEIL(0.2 * number of such customers). Return one row: top_customers (how many are in the top 20%), top_revenue (their revenue), total_revenue (revenue of all customers) and top_share_pct (top_revenue as a percentage of total_revenue, rounded to 1 decimal).
Starting Postgres in your browser…
Checking your access…