Task
You are given operational issue data from a ticketing system, a source lookup table, and a daily team activity table. Write a PostgreSQL query that returns each issue source with the number of open issues, the number of high-severity open issues, and the average days to resolve for issues that are already closed. Only include sources that have at least one open issue, and sort the result by open issue count descending, then source name ascending.
Schema
| Table | Column | Type | Description |
|---|
issues | issue_id | INT | Primary key for each issue |
issues | source_id | INT | Foreign key to issue_sources.source_id |
issues | severity | VARCHAR(20) | Severity label such as low, medium, or high |
issues | status | VARCHAR(20) | Issue status such as open or closed |
issues | created_at | DATE | Date the issue was created |
issues | resolved_at | DATE | Date the issue was resolved, if closed |
issue_sources | source_id | INT | Primary key for the issue source |
issue_sources | source_name | VARCHAR(50) | Human-readable source name |
daily_activity | activity_id | INT | Primary key for the activity row |
daily_activity | issue_id | INT | Foreign key to issues.issue_id |
daily_activity | activity_date | DATE | Date of the activity |
daily_activity | minutes_spent | INT | Minutes spent on the issue that day |
Sample data
issues
| issue_id | source_id | severity | status | created_at | resolved_at |
|---|
| 1 | 10 | high | open | 2024-05-01 | null |
| 2 | 10 | low | closed | 2024-05-02 | 2024-05-04 |
| 3 | 20 | high | closed | 2024-05-03 | 2024-05-06 |
| 4 | 20 | medium | open | 2024-05-04 | null |
| 5 | 30 | high | open | 2024-05-05 | null |
| 6 | 30 | low | closed | 2024-05-06 | 2024-05-07 |
| 7 | 40 | medium | closed | 2024-05-07 | 2024-05-10 |
| 8 | 40 | high | closed | 2024-05-08 | 2024-05-12 |
| 9 | 50 | low | open | 2024-05-09 | null |
| 10 | 60 | high | closed | 2024-05-10 | 2024-05-11 |
issue_sources
| source_id | source_name |
|---|
| 10 | Factory Line A |
| 20 | Factory Line B |
| 30 | Supplier Quality |
| 40 | Warehouse Intake |
| 50 | Logistics Delay |
| 60 | Test Bench |
| 70 | Packaging |
| 80 | Calibration |
daily_activity
| activity_id | issue_id | activity_date | minutes_spent |
|---|
| 1 | 1 | 2024-05-01 | 30 |
| 2 | 1 | 2024-05-02 | 45 |
| 3 | 2 | 2024-05-02 | 15 |
| 4 | 3 | 2024-05-03 | 60 |
| 5 | 4 | 2024-05-04 | 20 |
| 6 | 5 | 2024-05-05 | 90 |
| 7 | 6 | 2024-05-06 | 10 |
| 8 | 8 | 2024-05-08 | 25 |
| 9 | 9 | 2024-05-09 | 0 |
| 10 | 10 | 2024-05-10 | 35 |
Expected output
| source_name | open_issue_count | high_open_issue_count | avg_resolution_days |
|---|
| Factory Line A | 1 | 1 | 2.0 |
| Factory Line B | 1 | 0 | 3.0 |
| Logistics Delay | 1 | 0 | null |
| Supplier Quality | 1 | 1 | 1.0 |
