Lesson 6 of 10 · 30 min
Matching a specification and explaining it
By the end of this lesson you can translate every sentence of a metric specification into a specific clause, write the query that matches it exactly (including its exclusions and edge cases), and explain the logic in a clear 30-second narrative.
A specification (spec) is a precise definition of a result: which rows count, how they are grouped, how each measure is calculated, what is excluded. Analysts receive specs from finance, operations and product teams, and interviewers use them to test precision: every sentence of a good spec maps to a clause of the query, and a query that ignores one sentence is wrong even if it ‘looks right’.
The data in this lesson is Shifa Clinics (clinic): clinics, physicians and their service lines, patients, appointments (completed, no-show, cancelled or rescheduled), the services billed at each visit, invoices and insurance claim rejections. Notice before you start that invoices.insurer_id is the payer for that visit and can differ from the patient's current insurer.
Method: annotate the spec
Read the spec once, then go sentence by sentence and write the clause each sentence becomes:
| Spec sentence | Clause |
|---|---|
| “for appointments scheduled in 2025” | WHERE scheduled_at >= '2025-01-01' AND scheduled_at < '2026-01-01' |
| “per service line” | JOIN physicians … JOIN service_lines … + GROUP BY service_line |
| “no-show rate = no-shows ÷ (completed + no-shows)” | COUNT(*) FILTER (…) / NULLIF(COUNT(*) FILTER (…), 0) |
| “cancelled and rescheduled bookings are excluded from the rate” | they are in neither FILTER, so they never count |
| “every active physician, including those with no visits” | FROM physicians LEFT JOIN appointments … |
When the query is done, check the table again: every row of it must be visible in the SQL.
This lesson, its 5 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons