Write SQL and Python, run instantly in your browser, and track your progress.
You are a Customer Analytics Analyst at Amazon. The Marketing team wants to identify orders that are above or below each customer's typical spending. For each order, show the order details along with that customer's average order value and a flag indicating if this order is above average.
This requires using transform() to add group-level statistics to each row.
| Column Name | Type |
|---|---|
| order_id | int64 |
| order_number | object |
| customer_id |
You are a Customer Analytics Analyst at Amazon. The Marketing team wants to identify orders that are above or below each customer's typical spending. For each order, show the order details along with that customer's average order value and a flag indicating if this order is above average.
This requires using transform() to add group-level statistics to each row.
| Column Name | Type |
|---|---|
| order_id | int64 |
| order_number | object |
| customer_id |
| int64 |
| int64 |
| order_datetime | object |
| order_datetime | object |
| status | object |
| status | object |
| total_amount | float64 |
| total_amount | float64 |
| payment_status | object |
| payment_status | object |
| order_id | order_number | customer_id | order_datetime | status | total_amount | payment_status |
|---|---|---|---|---|---|---|
| 1 | ORD-10001 | 46 | 2025-08-20 07:01:34 | shipped | 55.32 | captured |
| 2 |
| order_id | order_number | customer_id | order_datetime | status | total_amount | payment_status |
|---|---|---|---|---|---|---|
| 1 | ORD-10001 | 46 | 2025-08-20 07:01:34 | shipped | 55.32 | captured |
| 2 |
| order_id | customer_id | total_amount | customer_avg | above_avg |
|---|---|---|---|---|
| 8 | 1 | 482.30 | 685.86 | False |
| 59 | 1 | 889.42 | 685.86 | True |
| 57 | 2 | 81.64 | 81.64 | False |
| order_id | customer_id | total_amount | customer_avg | above_avg |
|---|---|---|---|---|
| 8 | 1 | 482.30 | 685.86 | False |
| 59 | 1 | 889.42 | 685.86 | True |
| 57 | 2 | 81.64 | 81.64 | False |
Showing first 5 of 75 rows.
Showing first 5 of 75 rows.
1. Calculation:
2. Output:
1. Calculation:
2. Output:
| ORD-10002 |
| ORD-10002 |
| 19 |
| 19 |
| 2025-02-24 04:56:03 |
| 2025-02-24 04:56:03 |
| delivered |
| delivered |
| 662.48 |
| 662.48 |
| partial_refund |
| partial_refund |
| 3 | ORD-10003 | 21 | 2025-02-13 17:43:43 | shipped | 88.66 | captured |
| 3 | ORD-10003 | 21 | 2025-02-13 17:43:43 | shipped | 88.66 | captured |
| 4 | ORD-10004 | 8 | 2025-02-07 13:00:50 | shipped | 322.87 | captured |
| 4 | ORD-10004 | 8 | 2025-02-07 13:00:50 | shipped | 322.87 | captured |
| 5 | ORD-10005 | 8 | 2025-06-24 03:59:58 | packed | 629.34 | captured |
| 5 | ORD-10005 | 8 | 2025-06-24 03:59:58 | packed | 629.34 | captured |
| 13 | 3 | 1823.70 | 1039.47 | True |
| 13 | 3 | 1823.70 | 1039.47 | True |
| 61 | 3 | 255.25 | 1039.47 | False |
| 61 | 3 | 255.25 | 1039.47 | False |