Rank Top Failing Consent Workflows | Dataford Interview Questions

Your question is Rank Top Failing Consent Workflows. Start with the requirements and the three tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Wakemed Operations uses MyChart consent workflows for proxy access, releases, research participation, and interpreter-supported communications. Write a PostgreSQL query to identify the five workflows with the highest failure rates for selected patient segments.

Requirements

Include only attempts from the pediatric_proxy, limited_english, and older_adult segments between 2025-01-01 inclusive and 2025-04-01 exclusive.
Calculate total attempts, failed attempts, and failure rate percentage for each workflow.
Exclude workflows with fewer than two qualifying attempts.
Return the top five workflows, ordered by failure rate descending, failed attempts descending, and workflow name ascending to break ties.

Schema

consent_workflows

Column	Type	Description
workflow_idPK	INT	Unique consent workflow identifier
workflow_name	VARCHAR(150)	Display name of the consent workflow

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
user_segment	VARCHAR(50)	Operational segment assigned to the patient

consent_attempts

Column	Type	Description
attempt_idPK	INT	Unique consent attempt identifier
workflow_id	INT	Workflow used for the attempt
patient_id	INT	Patient associated with the attempt
status	VARCHAR(20)	Attempt outcome, such as completed or failed
attempted_at	TIMESTAMP	Timestamp when the consent attempt occurred