Task
You are given seller order data from a marketplace. Write a PostgreSQL query that identifies the sellers whose revenue is growing fastest month over month, based on the largest percentage increase in the latest month compared with the previous month. Return one row per seller with their latest month, previous month revenue, growth amount, and growth rate, then rank sellers from highest to lowest growth rate.
Use window functions to compare each seller’s monthly revenue to the prior month. Exclude sellers who do not have revenue in both months needed for the comparison.
Schema
| table | column | type | description |
|---|
| seller_orders | order_id | INT | Primary key for each order |
| seller_orders | seller_id | INT | Seller identifier |
| seller_orders | seller_name | VARCHAR(255) | Seller display name |
| seller_orders | order_date | DATE | Order completion date |
| seller_orders | order_revenue | DECIMAL(10,2) | Revenue from the order |
Sample data
| order_id | seller_id | seller_name | order_date | order_revenue |
|---|
| 1 | 101 | NorthPeak Finds | 2024-01-05 | 120.00 |
| 2 | 101 | NorthPeak Finds | 2024-02-11 | 180.00 |
| 3 | 101 | NorthPeak Finds | 2024-03-02 | 240.00 |
| 4 | 102 | Vintage Vault | 2024-01-09 | 300.00 |
| 5 | 102 | Vintage Vault | 2024-02-14 | 150.00 |
| 6 | 102 | Vintage Vault | 2024-03-18 | 225.00 |
| 7 | 103 | Cozy Corner Co | 2024-02-03 | 80.00 |
| 8 | 103 | Cozy Corner Co | 2024-03-07 | 200.00 |
| 9 | 104 | FlashDrop Studio | 2024-01-20 | 0.00 |
| 10 | 104 | FlashDrop Studio | 2024-02-22 | 50.00 |
| 11 | 105 | Null Harbor | 2024-03-10 | 75.00 |
Expected output
| seller_id | seller_name | latest_month | previous_month | previous_revenue | latest_revenue | revenue_growth | growth_rate | growth_rank |
|---|
| 103 | Cozy Corner Co | 2024-03 | 2024-02 | 80.00 | 200.00 | 120.00 | 1.5000 | 1 |
| 104 | FlashDrop Studio | 2024-02 | 2024-01 | 0.00 | 50.00 | 50.00 | null | 2 |
| 101 | NorthPeak Finds | 2024-03 | 2024-02 | 180.00 | 240.00 | 60.00 | 0.3333 | 3 |
| 102 | Vintage Vault | 2024-03 | 2024-02 | 150.00 | 225.00 | 75.00 | 0.5000 | 4 |
