Task
You are given raw player transaction data from an Activision commerce feed. Build a summary view that identifies the top-performing SKUs by revenue and projects each SKU’s next-quarter performance using recent quarterly trends.
Your result should show the top SKUs ranked by current-quarter revenue, along with their quarter-over-quarter growth rate and a simple next-quarter revenue projection based on the latest two completed quarters. Use PostgreSQL syntax only.
Schema
| Table | Column | Type | Description |
|---|
player_transactions | transaction_id | BIGINT | Unique transaction identifier |
player_transactions | player_id | BIGINT | Player who made the purchase |
player_transactions | sku_id | VARCHAR(50) | Purchased SKU identifier |
player_transactions | sku_name | VARCHAR(255) | Display name of the SKU |
player_transactions | transaction_ts | TIMESTAMP | Transaction timestamp |
player_transactions | units | INT | Units purchased |
player_transactions | gross_revenue | NUMERIC(12,2) | Revenue from the transaction |
player_transactions | currency_code | CHAR(3) | Currency code |
player_transactions | transaction_status | VARCHAR(20) | Status such as completed, refunded, or pending |
Sample data
| transaction_id | player_id | sku_id | sku_name | transaction_ts | units | gross_revenue | currency_code | transaction_status |
|---|
| 1001 | 501 | SKIN_A1 | Neon Ranger Skin | 2024-01-15 10:12:00 | 1 | 9.99 | USD | completed |
| 1002 | 502 | BUNDLE_X2 | Battle Pass Bundle | 2024-02-03 14:20:00 | 1 | 24.99 | USD | completed |
| 1003 | 503 | SKIN_A1 | Neon Ranger Skin | 2024-03-19 09:05:00 | 1 | 9.99 | USD | refunded |
| 1004 | 504 | COIN_500 | 500 Coin Pack | 2024-04-02 18:44:00 | 1 | 4.99 | USD | completed |
| 1005 | 505 | BUNDLE_X2 | Battle Pass Bundle | 2024-04-18 11:30:00 | 1 | 24.99 | USD | completed |
| 1006 | 506 | SKIN_A1 | Neon Ranger Skin | 2024-05-07 16:10:00 | 1 | 9.99 | USD | completed |
| 1007 | 507 | COIN_500 | 500 Coin Pack | 2024-06-21 20:01:00 | 2 | 9.98 | USD | completed |
| 1008 | 508 | EMOTE_Z9 | Victory Emote | 2024-06-30 08:15:00 | 1 | 2.99 | USD | pending |
Expected output
| sku_id | sku_name | current_quarter_revenue | previous_quarter_revenue | qoq_growth_rate | next_quarter_revenue_projection | revenue_rank |
|---|
| BUNDLE_X2 | Battle Pass Bundle | 24.99 | 24.99 | 0.0000 | 24.99 | 1 |
| SKIN_A1 | Neon Ranger Skin | 9.99 | 9.99 | 0.0000 | 9.99 | 2 |
| COIN_500 | 500 Coin Pack | 14.97 | 4.99 | 2.0000 | 24.95 | 3 |
