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`. Calculate `point_difference` as the subtask's story points minus the parent's story points. Sort by `point_difference` descending, then `subtask_key` descending. ### Table Schema | Table | Column | Type | |---|---|---| | `jira_issues` | `issue_id` | integer | | `jira_issues` | `issue_key` | varchar(20) | | `jira_issues` | `issue_type` | varchar(20) | | `jira_issues` | `summary` | varchar(120) | | `jira_issues` | `parent_issue_id` | integer | | `jira_issues` | `story_points` | decimal(6,2) | | `jira_issues` | `project_key` | varchar(20) | | `jira_issues` | `status` | varchar(30) | ### Example Input: `jira_issues` | issue_id | issue_key | issue_type | summary | parent_issue_id | story_points | project_key | status | |---:|---|---|---|---:|---:|---|---| | 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 | ### Example Output Explanation `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.
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');