Business Context
You’re on the data platform team supporting a React/Redux web app used by ~3M monthly active users. Engineering leadership tracks reliability via a JIRA dashboard that shows “open bugs by team” and drives weekly staffing decisions. A recent change to the event ingestion pipeline started emitting duplicate status-change events (e.g., the same ticket transitioning to In Progress twice within seconds), causing the dashboard to overcount open bugs and triggering a high-priority JIRA bug for the analytics team.
The source-of-truth is a relational warehouse where tickets live in jira_issues, while status changes are append-only in jira_issue_status_events. Your job is to write a SQL query that produces the correct “current open bug count by team” by deduplicating noisy status events.
Task
Write a SQL query to compute the number of currently open bugs per team as of 2026-02-01 00:00:00.
Requirements
- Only include issues where
issue_type = 'Bug'.
- Determine each issue’s latest status as of
2026-02-01 00:00:00.
- Deduplicate status events where the same
issue_id has the same to_status multiple times; keep only the most recent event per (issue_id, to_status).
- Count an issue as open if its latest status is in:
('To Do','In Progress','Blocked').
- Output columns:
team, open_bug_count.
- Sort by
open_bug_count descending, then team ascending.
Table Definitions
jira_issues
| column | type | description |
|---|
| issue_id | BIGINT | Primary key |
| issue_key | VARCHAR(20) | Human-readable key (e.g., WEB-123) |
| issue_type | VARCHAR(50) | e.g., Bug, Task, Story |
| team | VARCHAR(50) | Owning team (e.g., Web-Platform) |
| created_at | TIMESTAMP | Issue creation time |
jira_issue_status_events
| column | type | description |
|---|
| event_id | BIGINT | Primary key |
| issue_id | BIGINT | Foreign key to jira_issues.issue_id |
| changed_at | TIMESTAMP | When the status change occurred |
| from_status | VARCHAR(50) | Previous status |
| to_status | VARCHAR(50) | New status |
Sample Data
jira_issues
| issue_id | issue_key | issue_type | team | created_at |
|---|
| 101 | WEB-101 | Bug | Web-Platform | 2026-01-10 09:12:00 |
| 102 | WEB-102 | Bug | Checkout | 2026-01-12 15:20:00 |
| 103 | WEB-103 | Task | Web-Platform | 2026-01-15 11:05:00 |
| 104 | WEB-104 | Bug | Checkout | 2026-01-20 08:45:00 |
| 105 | WEB-105 | Bug | Web-Platform | 2026-01-25 13:30:00 |
jira_issue_status_events
| event_id | issue_id | changed_at | from_status | to_status |
|---|
| 1001 | 101 | 2026-01-10 09:12:00 | NULL | To Do |
| 1002 | 101 | 2026-01-11 10:00:00 | To Do | In Progress |
| 1003 | 101 | 2026-01-11 10:00:02 | To Do | In Progress |
| 1004 | 102 | 2026-01-12 15:20:00 | NULL | To Do |
| 1005 | 102 | 2026-01-13 09:00:00 | To Do | Done |
| 1006 | 104 | 2026-01-20 08:45:00 | NULL | To Do |
| 1007 | 104 | 2026-01-21 12:00:00 | To Do | Blocked |
| 1008 | 105 | 2026-01-25 13:30:00 | NULL | To Do |
| 1009 | 105 | 2026-01-26 16:00:00 | To Do | In Progress |
| 1010 | 105 | 2026-01-26 16:00:01 | To Do | In Progress |
Expected Output
| team | open_bug_count |
|---|
| Web-Platform | 2 |
| Checkout | 1 |
