Unpack Diagnosis Array Counts | Dataford Interview Questions

Given a JSON payload containing an array of medical diagnoses, write a query to unpack the array and count occurrences of each diagnosis.

Use the medical_payloads table. Ignore rows with a missing, non-array, null, or blank diagnosis value. Preserve distinct diagnosis text after trimming surrounding whitespace.

Output

One row per diagnosis with columns diagnosis and occurrence_count.
Exclude null and blank diagnoses.
Order by occurrence_count descending, then diagnosis ascending.

Schema

medical_payloads

Column	Type	Description
payload_idPK	INT	Unique identifier for the JSON payload
payload	JSONB	JSON document that may contain a diagnoses array

Your question is Unpack Diagnosis Array Counts. Start with the requirements and the one table in the Question tab.

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

Problem