Task
You are given ticket, region, and product data. Write a PostgreSQL query that identifies whether a recent spike in tickets is concentrated in a single region or a single product line by comparing ticket volume in the last 7 days to the prior 7 days. Return each region and product line with its current-period ticket count, prior-period ticket count, and percentage change, then sort the biggest spikes first.
Schema
| Table | Column | Type | Description |
|---|
tickets | ticket_id | INT | Primary key for each ticket |
tickets | created_at | DATE | Date the ticket was created |
tickets | region_id | INT | Region linked to the ticket |
tickets | product_id | INT | Product linked to the ticket |
regions | region_id | INT | Primary key for region |
regions | region_name | VARCHAR(50) | Human-readable region name |
products | product_id | INT | Primary key for product |
products | product_line | VARCHAR(50) | Product line name |
Sample data
| tickets.ticket_id | created_at | region_id | product_id |
|---|
| 1 | 2024-05-01 | 1 | 10 |
| 2 | 2024-05-02 | 1 | 10 |
| 3 | 2024-05-03 | 1 | 11 |
| 4 | 2024-05-04 | 2 | 10 |
| 5 | 2024-05-09 | 1 | 10 |
| 6 | 2024-05-10 | 1 | 10 |
| 7 | 2024-05-10 | 3 | 12 |
| 8 | 2024-05-11 | 1 | 10 |
| 9 | 2024-05-12 | 1 | 11 |
| 10 | 2024-05-12 | 2 | 12 |
| regions.region_id | region_name |
|---|
| 1 | North America |
| 2 | EMEA |
| 3 | APAC |
| products.product_id | product_line |
|---|
| 10 | AVEVA PI System |
| 11 | AVEVA Historian |
| 12 | AVEVA MES |
Expected output
| segment_type | segment_name | current_7d_tickets | prior_7d_tickets | pct_change |
|---|
| region | North America | 5 | 3 | 66.67 |
| product_line | AVEVA PI System | 5 | 3 | 66.67 |
| region | EMEA | 1 | 1 | 0.00 |
| product_line | AVEVA Historian | 2 | 2 | 0.00 |
| region | APAC | 1 | 0 | 100.00 |
| product_line | AVEVA MES | 1 | 1 | 0.00 |
