Subtask Failures
ASKED IN INTERVIEWLinked In
Problem Statement
Find parent tasks that have at least one child subtask with a status of Failed.
Rules:
- Columns: parent_task_id, parent_task_name, failed_subtask_count.
- Only include parents with failed children.
- Sort by failed_subtask_count DESC.
Tests your understanding of
Aggregation, Joins and Filtering
Input Tables
tasks
| task_id(INTEGER) | task_name(VARCHAR) |
|---|---|
| 1 | Pipeline_A |
| 2 | Pipeline_B |
| 10 | Child_1 |
| 11 | Child_2 |
| 20 | Child_3 |
subtask_relations
| parent_id(INTEGER) | child_id(INTEGER) |
|---|---|
| 1 | 10 |
| 1 | 11 |
| 2 | 20 |
execution_logs
| task_id(INTEGER) | status(VARCHAR) |
|---|---|
| 10 | Failed |
| 11 | Failed |
| 20 | Success |
Expected Output
| parent_task_id(INTEGER) | parent_task_name(VARCHAR) | failed_subtask_count(INTEGER) |
|---|---|---|
| 1 | Pipeline_A | 2 |
Tags
HardASKED IN INTERVIEWAggregationJoinsFiltering
25 min
47%