Company: Atlassian | Difficulty: medium
Atlassian's Jira planning team wants to find subtasks whose estimated effort is greater than the effort estimate of their parent issue.
For every subtask, compare its story-point estimate with the story-point estimate of its direct parent issue. Return only subtasks where the subtask has strictly more story points than its parent.
Ignore subtasks without a parent, subtasks whose parent has no estimate, and subtasks with no estimate.
Return the columns in this exact order: subtask_key, subtask_summary, parent_key, subtask_points, parent_points, and point_difference.
subtask_key
subtask_summary
parent_key
subtask_points
parent_points
point_difference
Calculate point_difference as the subtask's story points minus the parent's story points. Sort by point_difference descending, then subtask_key descending.
jira_issues
issue_id
issue_key
issue_type
summary
parent_issue_id
story_points
project_key
status
APP-11 has 8 story points while its parent APP-10 has 5, so its point difference is 3. APP-12 is excluded because its estimate is lower than its parent's. APP-21 has 5 points compared with its parent's 2, so its point difference is 3.
APP-11
APP-10
APP-12
APP-21
DROP TABLE IF EXISTS jira_issues; CREATE TABLE jira_issues ( issue_id INT, issue_key VARCHAR(20), issue_type VARCHAR(20), summary VARCHAR(120), parent_issue_id INT, story_points DECIMAL(6,2), project_key VARCHAR(20), status VARCHAR(30) ); INSERT INTO jira_issues VALUES (1,'APP-10','Story','Checkout redesign',NULL,5.00,'APP','In Progress'), (2,'APP-11','Sub-task','Update payment form',1,8.00,'APP','To Do'), (3,'APP-12','Sub-task','Add validation tests',1,3.00,'APP','Done'), (4,'APP-20','Task','Logging upgrade',NULL,2.00,'APP','To Do'), (5,'APP-21','Sub-task','Update dashboards',4,5.00,'APP','To Do');
← All SQL Challenges · Atlassian Questions · Learn SQL Free