Business Context
Eaton's business analysts need to identify customers showing sustained purchasing activity across consecutive calendar months. The analysis should use completed orders for Eaton electrical products and ignore duplicate orders within the same month.
Task
Write a PostgreSQL query that identifies customers who placed qualifying orders in at least one pair of consecutive calendar months.
Requirements
- Consider only orders with
order_status = 'Completed', non-null order dates, and products in the Electrical business unit.
- Treat multiple qualifying orders by the same customer in one month as one active month.
- Use a window function to detect consecutive calendar months, including December to January transitions.
- Return each qualifying customer once with the number of consecutive month pairs, the first pair, and the last pair.
- Sort the output by
customer_id ascending.
Representative Data
customers includes customers without qualifying orders, while orders includes canceled, undated, and non-qualifying examples. Multiple rows can belong to the same customer or order.
