D8LooPFocus modeCODE WORKSPACE
Top buyers by revenue
Return buyer_id, revenue (summed total_amount per buyer), and revenue_rank for the top 3 buyers by revenue. Order by revenue_rank, then buyer_id.
buyer_revenue CTE → ranked CTE with RANK() OVER (ORDER BY revenue DESC) → filter revenue_rank <= 3.
Sample input
ordersAll-status buyer totals: 6→700, 2→650, 1→330, 3→290, 4→190.
| order_id | buyer_id | total_amount |
|---|---|---|
| 1010 | 6 | 640 |
| 1006 | 6 | 60 |
| 1005 | 2 | 540 |
| 1011 | 2 | 110 |
| 1001 | 1 | 80 |
Expected output
Expected outputTop 3 buyers by revenue, ranked. Buyer 3 (290) is rank 4 and drops out.
| buyer_id | revenue | revenue_rank |
|---|---|---|
| 6 | 700 | 1 |
| 2 | 650 | 2 |
| 1 | 330 | 3 |
Constraints
Aggregate revenue per buyer in a CTE, then RANK() OVER (ORDER BY revenue DESC) in a second layer, and keep revenue_rank <= 3. Order by revenue_rank, buyer_id.
Expected skills
Ranking with RANK, layered CTEs, and tie-aware top-N.
SQL
Loading...
AI evaluation
Run the SQL query to inspect preview rows.