Rank Customers by Monthly Revenue with Competition Ties

Calculate each customer's revenue by calendar month, rank customers within each month, and return customers whose revenue rank is within a supplied limit.

Input Tables

revenue_events

Column	Type	Meaning
event_id	INTEGER	Primary key
customer_id	INTEGER	Non-null customer identifier
revenue_date	DATE	Non-null date
amount	NUMERIC(14,2)	Non-null, nonnegative revenue

ranking_policy(top_rank INTEGER NOT NULL) contains exactly one row with a positive integer limit.

Output Contract

Write one read-only PostgreSQL query returning month_start, customer_id, total_revenue, and revenue_rank.

Aggregate all events for a customer in the same calendar month. Rank those monthly customer totals descending with competition ranking: tied totals receive the same rank and the next rank skips positions. Return ranks less than or equal to top_rank, ordered by month_start ascending, revenue_rank ascending, and customer_id ascending. month_start is the first day of the month as a DATE.

Example

For one January, customers 1 and 2 each have total revenue 100.00, and customer 3 has 90.00. If top_rank = 2, the result includes customers 1 and 2, both with rank 1. Customer 3 has rank 3 and is excluded.

Constraints and Clarifications

• The rank limit is an explicit input rather than an invented fixed top-customer count.
• Ties use RANK semantics, not dense ranking or an arbitrary unique ordering.
• The output's customer-ID tie-break controls display order only; it must not break equal-revenue ranks.
• A customer without an event in a month has no row for that month.
• Multiple events for the same customer and month must be summed before ranking.

Hint: Rank the aggregated rows

The ranking competition is between customer-month totals, not between individual revenue events.

Top-Ranked Customers by Monthly Revenue

Table revenue_events

column	type	constraints
event_id	INTEGER	PRIMARY KEY
customer_id	INTEGER	NOT NULL
revenue_date	DATE	NOT NULL
amount	NUMERIC(14,2)	NOT NULL

Sample data:

amount	customer_id	event_id	revenue_date
75.25	1	1	2024-12-01
24.75	1	2	2024-12-15
500.00	2	3	2024-12-31
99.99	5	4	2024-12-20
60.00	1	5	2025-01-01
40.00	1	6	2025-01-31
100.00	2	7	2025-01-15
50.00	3	8	2025-01-20
40.00	3	9	2025-01-25
80.00	4	10	2025-01-10
250.00	3	11	2025-02-01
120.00	1	12	2025-02-28
120.00	4	13	2025-02-05
0.00	2	14	2025-02-14

Table ranking_policy

column	type	constraints
top_rank	INTEGER	NOT NULL

Sample data:

top_rank
2

Expected output

month_start	customer_id	total_revenue	revenue_rank
2024-12-01	2	500.00	1
2024-12-01	1	100.00	2
2025-01-01	1	100.00	1
2025-01-01	2	100.00	1
2025-02-01	3	250.00	1
2025-02-01	1	120.00	2
2025-02-01	4	120.00	2

Hints

1. The ranking competition is between customer-month totals, not between individual revenue events.