Modules
Data Analyst
Data Engineer
Product Data Scientist
AI Engineer
Machine Learning Engineer
Prompt Engineer
Using the same table as on the previous practice, users and orders.
Your objective is to measure the total spend for users grouped by their number of orders, and then calculate the cumulative sum of these totals in ascending order of the number of orders.
Hint: We first identify all users who have made exactly 1 order and sum up the total amount spent by these users. Next, we do the same for users who have made exactly 2 orders, then for those with 3 orders, and so on. And then do your magic using window functions.
Table used:
users
| Column Name | Description |
|---|---|
| user_id | Unique identifier for each user. |
| username | The username chosen by the user. |
| country | The country where the user is located. |
orders
| Column Name | Description |
|---|---|
| order_id | Unique identifier for each order. |
| buyer_id | The user_id of the user who placed the order. |
| seller_id | The user_id of the user who is selling the item. |
| order_amount | The total amount of the order in currency units. |
| nb_of_orders | total_spent | cumulative_sum |
|---|---|---|
| 1 | 3850.00 | 3850.00 |
| 2 | 4450.00 | 8300.00 |
| 3 | 530.00 | 8830.00 |
| 4 | 780.00 | 9610.00 |
Using the same table as on the previous practice, users and orders.
Your objective is to measure the total spend for users grouped by their number of orders, and then calculate the cumulative sum of these totals in ascending order of the number of orders.
Hint: We first identify all users who have made exactly 1 order and sum up the total amount spent by these users. Next, we do the same for users who have made exactly 2 orders, then for those with 3 orders, and so on. And then do your magic using window functions.
Table used:
users
| Column Name | Description |
|---|---|
| user_id | Unique identifier for each user. |
| username | The username chosen by the user. |
| country | The country where the user is located. |
orders
| Column Name | Description |
|---|---|
| order_id | Unique identifier for each order. |
| buyer_id | The user_id of the user who placed the order. |
| seller_id | The user_id of the user who is selling the item. |
| order_amount | The total amount of the order in currency units. |
| nb_of_orders | total_spent | cumulative_sum |
|---|---|---|
| 1 | 3850.00 | 3850.00 |
| 2 | 4450.00 | 8300.00 |
| 3 | 530.00 | 8830.00 |
| 4 | 780.00 | 9610.00 |