Bug Fix Rate
ASKED IN INTERVIEWAtlassian
Problem Statement
Product managers at Atlassian are tracking the efficiency of the development team in squashing bugs. They need to compare the average time it takes to resolve "Critical" bugs versus "Major" bugs to ensure resources are allocated correctly.
Write an SQL query to find the average resolution time in hours for these two severity levels.
Rules:
- A resolution time is defined as the difference between resolved_at and created_at.
- Only include tickets where the severity is either Critical or Major.
- Only include tickets that have been successfully resolved (resolved_at is not null).
- Average resolution time should be rounded to 2 decimal places.
- Output columns: severity, average_resolution_hours.
- Sort the results by average_resolution_hours in descending order.
Table Schema:
- Tickets: ticket_id, project_id, severity (Critical, Major, Minor), created_at, resolved_at.
Tests your understanding of
DateTime, Aggregation, Filtering and Case When
Input Tables
Tickets
| ticket_id(INTEGER) | severity(VARCHAR) | created_at(TIMESTAMP) | resolved_at(TIMESTAMP) |
|---|---|---|---|
| 1 | Critical | 2026-02-01 08:00:00 | 2026-02-01 12:00:00 |
| 2 | Critical | 2026-02-01 10:00:00 | 2026-02-01 16:00:00 |
| 3 | Major | 2026-02-01 09:00:00 | 2026-02-02 09:00:00 |
| 4 | Major | 2026-02-02 10:00:00 | 2026-02-03 10:00:00 |
| 5 | Minor | 2026-02-01 08:00:00 | 2026-02-01 10:00:00 |
| 6 | Critical | 2026-02-03 08:00:00 | null |
Expected Output
| severity(VARCHAR) | average_resolution_hours(DECIMAL) |
|---|---|
| Major | 24 |
| Critical | 5 |
Tags
MediumASKED IN INTERVIEWDateTimeAggregationFilteringCase When
15-20 min
58%