Write SQL and Python, run instantly in your browser, and track your progress.
You are a Revenue Analyst at Snowflake. The Finance team needs invoice metrics by status in a long format for their reporting dashboard. Group invoices by status, calculate multiple metrics (count, total_cents sum, and average total_cents), then reshape to have one row per status-metric combination.
| Column Name | Type |
|---|---|
| invoice_id | int64 |
| status | object |
| total_cents | int64 |
You are a Revenue Analyst at Snowflake. The Finance team needs invoice metrics by status in a long format for their reporting dashboard. Group invoices by status, calculate multiple metrics (count, total_cents sum, and average total_cents), then reshape to have one row per status-metric combination.
| Column Name | Type |
|---|---|
| invoice_id | int64 |
| status | object |
| total_cents | int64 |
| invoice_id | status | total_cents |
|---|---|---|
| 1 | paid | 15000 |
| 2 | paid | 22500 |
| 3 | open | 18000 |
| 4 | paid | 30000 |
| 5 | void | 12000 |
| 6 |
| invoice_id | status | total_cents |
|---|---|---|
| 1 | paid | 15000 |
| 2 | paid | 22500 |
| 3 | open | 18000 |
| 4 | paid | 30000 |
| 5 | void | 12000 |
| 6 |
| status | metric | value |
|---|---|---|
| draft | avg_cents | 193814.86 |
| draft | count | 7 |
| draft | sum_cents | 1356704 |
| open | avg_cents | 1242438.33 |
| open | count | 6 |
| status | metric | value |
|---|---|---|
| draft | avg_cents | 193814.86 |
| draft | count | 7 |
| draft | sum_cents | 1356704 |
| open | avg_cents | 1242438.33 |
| open | count | 6 |
Showing first 5 of 24 rows.
Showing first 5 of 24 rows.
1. Data Selection:
2. Aggregation:
3. Reshaping:
4. Output:
1. Data Selection:
2. Aggregation:
3. Reshaping:
4. Output:
| open |
| open |
| 25000 |
| 25000 |
| 7 | paid | 27500 |
| 7 | paid | 27500 |