Priority mix per project
mediumPriority mix per project
Atlassian SQL Interview Question
Atlassian staffs teams from the shape of a backlog, not its size. A project of forty low priority items needs one engineer working steadily; a project with six of the highest priority needs three people and a daily check-in. Staffing therefore starts from how many tickets each project is carrying at each priority level.
Return the project, and the number of tickets at each level as p1_tickets, p2_tickets and p3_tickets. A level a project has none of counts as zero.
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 | p1_tickets | p2_tickets | p3_tickets |
|---|---|---|---|
| Bitbucket | 1 | 1 | 1 |
| Jira Cloud | 1 | 3 | 2 |
Explanation
Jira Cloud has one ticket at the top level, three in the middle and two at the lowest. Bitbucket has one of each. Both carry a single top-priority ticket, so it is the levels underneath that decide who staffs them.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Atlassian
Difficulty
medium
Topic
reshaping
Language
SQL
Your query
Starting the SQL engine...