SQL Joins and Aggregations | Dataford Interview Questions

Your question is SQL Joins and Aggregations. 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

Align Technology wants to review Invisalign scan activity across provider practices. Write a PostgreSQL query that summarizes qualifying scans by practice for the first quarter of 2025.

Requirements

Use INNER JOIN to connect practices, patients, and Invisalign scans.
Include only scans with scan_status = 'Completed' from January 1 through March 31, 2025.
Return each practice name, the number of distinct patients, the number of completed scans, average scan duration, and total scan file size.
Include only practices with at least two qualifying scans, ordered by scan count descending and practice name ascending.

Schema

practices

Column	Type	Description
practice_idPK	INT	Unique provider practice identifier
practice_name	VARCHAR(120)	Provider practice name
region	VARCHAR(40)	Practice operating region

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
practice_id	INT	Practice associated with the patient
full_name	VARCHAR(120)	Patient full name

scans

Column	Type	Description
scan_idPK	INT	Unique intraoral scan identifier
patient_id	INT	Patient associated with the scan
scan_date	DATE	Date the scan was captured
scan_status	VARCHAR(20)	Processing status of the scan
duration_seconds	INT	Scan duration in seconds
file_size_mb	DECIMAL(8,2)	Digital scan file size in megabytes