SQL Window Functions for Top Customers

MediumSQL · PostgreSQL

Problem

Genesys Cloud Analytics needs a ranked view of customers based on completed subscription revenue. Write a PostgreSQL query that aggregates revenue per customer and uses a window function to return the top 10 customers.

Requirements

Join customers to orders using customer_id.
Include only orders with status = 'completed'.
Calculate total revenue per customer, treating a customer with only NULL revenue values as zero.
Use a window function to assign the revenue rank, return the top 10 ranks, and order the final result from highest to lowest revenue. Break ties by customer_id.

Output details

Return columns in this order: customer_name, total_revenue, and revenue_rank.
Exclude customers with no completed orders.
Represent total_revenue as NUMERIC(12,2), retaining exactly two decimal places.
Order tied revenues by customer_id ascending.
Assign unique sequential revenue_rank values rather than sharing ranks among tied revenues.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(120)	Customer account name
segment	VARCHAR(40)	Customer segment

orders

Column	Type	Description
order_idPK	INT	Unique order identifier
customer_id	INT	References customers.customer_id
revenue	NUMERIC(12,2)	Revenue recognized for the order
status	VARCHAR(20)	Order processing status
order_date	DATE	Date the order was placed

Tablescustomersorders

Requirements

Join customers to orders using customer_id.
Include only orders with status = 'completed'.
Calculate total revenue per customer, treating a customer with only NULL revenue values as zero.
Use a window function to assign the revenue rank, return the top 10 ranks, and order the final result from highest to lowest revenue. Break ties by customer_id.

Output details

Return columns in this order: customer_name, total_revenue, and revenue_rank.
Exclude customers with no completed orders.
Represent total_revenue as NUMERIC(12,2), retaining exactly two decimal places.
Order tied revenues by customer_id ascending.
Assign unique sequential revenue_rank values rather than sharing ranks among tied revenues.