Rank Customers by Monthly Revenue with Competition Ties
Company: Agoda
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
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.
```
Overview: Aggregate customer revenue by month and return top competition ranks, preserving tied totals and deterministic display order.
Read the full Agoda Data Engineer interview experience this question came from
Calculate each customer's revenue by calendar month, rank customers within each month, and return customers whose revenue rank is within a supplied limit.
Table revenue_events(event_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, revenue_date DATE NOT NULL, amount NUMERIC(14,2) NOT NULL) holds revenue events; amount is nonnegative.
Table ranking_policy(top_rank INTEGER NOT NULL) contains exactly one row with a positive integer limit.
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 (total_revenue is the sum of amount). Rank those monthly customer totals descending within each month with competition ranking: tied totals receive the same rank and the next rank skips positions. Return only rows whose revenue_rank is less than or equal to ranking_policy.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.
Clarifications:
- The rank limit is an explicit input read from ranking_policy rather than a 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.
Tables
revenue_events(event_id INTEGER, customer_id INTEGER, revenue_date DATE, amount NUMERIC(14,2))
ranking_policy(top_rank INTEGER)
Hints
- The ranking competition is between customer-month totals, not between individual revenue events.