Orders per Customer per Month (PySpark)

PYSPARK coding challenge · Difficulty: medium · +75 XP

Problem

Using the PySpark DataFrame API, count the number of orders each customer placed per month (month bucketed to its first day, e.g. 2024-01-01).

Tables

Table: sales

| order_id | customer_id | product_id | amount | order_date |
| --- | --- | --- | --- | --- |
| 201 | 1 | 11 | 100 | 2024-01-05 |
| 202 | 1 | 12 | 200 | 2024-01-20 |
| 203 | 1 | 11 | 150 | 2024-02-10 |
| 204 | 2 | 13 | 300 | 2024-01-15 |
| 205 | 2 | 12 | 250 | 2024-02-05 |
| 206 | 2 | 11 | 400 | 2024-02-25 |

Expected Output

| customer_id | month | order_count |
| --- | --- | --- |
| 1 | 2024-01-01 | 2 |
| 1 | 2024-02-01 | 1 |
| 2 | 2024-01-01 | 1 |
| 2 | 2024-02-01 | 2 |

• Return: customer_id, month, order_count
• Approach: groupBy('customer_id', F.date_format('order_date','yyyy-MM-01')) → count('*')