Slowest ticket per project
mediumSlowest ticket per project
Atlassian SQL Interview Question
Averages hide the tickets that damage a team's reputation. One item that sat for three weeks is what a customer remembers and what gets raised in a quarterly review, while forty quick fixes around it pull the average down. Each project lead reviews their single worst case, and a ticket still open has no duration yet.
For each project, return the project, the ticket_id that took longest to resolve, and the number of days it took as days_open.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
- Data Engineer
- Data Scientist
atlassian_ticketsTable24 rows
| Column Name | Type |
|---|---|
| ticket_id | BIGINT |
| parent_ticket_id | BIGINT |
| project | VARCHAR |
| opened_on | DATE |
| resolved_on | DATE |
| priority | VARCHAR |
| assignee_id | BIGINT |
atlassian_ticketsExample Input
| ticket_id | parent_ticket_id | project | opened_on | resolved_on | priority | assignee_id |
|---|---|---|---|---|---|---|
| 2001 | NULL | Jira Cloud | 2024-02-01 | 2024-02-14 | P1 | 11 |
| 2002 | 2001 | Jira Cloud | 2024-02-02 | 2024-02-06 | P2 | 12 |
| 2003 | 2001 | Jira Cloud | 2024-02-03 | 2024-02-11 | P2 | 13 |
| 2004 | 2001 | Jira Cloud | 2024-02-05 | NULL | P3 | 12 |
| 2005 | NULL | Jira Cloud | 2024-02-04 | 2024-02-09 | P2 | 14 |
| 2006 | 2005 | Jira Cloud | 2024-02-05 | 2024-02-08 | P3 | 11 |
| 2014 | NULL | Bitbucket | 2024-02-01 | 2024-02-03 | P3 | 17 |
| 2015 | NULL | Bitbucket | 2024-02-05 | 2024-02-22 | P1 | 18 |
| 2016 | 2015 | Bitbucket | 2024-02-06 | 2024-02-13 | P2 | 17 |
Example Output
| project | ticket_id | days_open |
|---|---|---|
| Bitbucket | 2015 | 17 |
| Jira Cloud | 2001 | 13 |
Explanation
Bitbucket's worst case is 2015 at 17 days and Jira Cloud's is 2001 at 13. Tickets still open have no resolution date and so no duration, which is why they are left out rather than sorted to one end.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Atlassian
Difficulty
medium
Topic
ranking
Language
SQL
Your query
Starting the SQL engine...