How would you approach a take-home project in R or Python to deliver a clear analysis and results?
For this SQL-focused exercise, use the provided analysis-run, subject, and observation data. Return a reproducible comparison of the two latest completed runs, including coverage, metric changes, data-quality indicators, and an impact grouping.
Output
- One row per subject included in either selected run
- Columns:
subject_id, subject_name, latest_value, prior_value, change_value, latest_quality_flags, impact_quartile, and result_status
- Include subjects missing from one run and identify quality or coverage issues
- Order by
impact_quartile, then subject_id
Output details
- Select the two completed runs by
run_started_at descending, breaking ties by run_id descending.
- Include every subject from
subjects, including subjects absent from both selected runs.
- Average observations per subject-run, round to two decimals, and list distinct non-null quality flags alphabetically separated by
, ; otherwise return NULL.
- Treat zero values as present, and classify statuses in precedence order:
missing_latest, missing_prior, quality_review when flags contain warning, declined for negative changes, otherwise improved_or_stable.
- Assign four
impact_quartile buckets by absolute change descending, place NULL changes last, and break ties by subject_id ascending.
- Order the final rows by
impact_quartile ascending, then subject_id ascending.
