SQL Query With Joins and Grouping | Dataford Interview Questions

Your question is SQL Query With Joins and Grouping. 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

GSK's Veeva Vault CTMS reporting team needs a concise enrollment summary for Phase 3 clinical trials during 2025. Write a PostgreSQL query that combines trial, site, and enrollment data.

Requirements

Use INNER JOIN to connect trials, trial_sites, and enrollments.
Include only Phase 3 enrollments recorded during calendar year 2025.
Group results by trial and country, returning the number of distinct sites and total participants.
Exclude groups with no positive enrollment, then sort by total participants descending, followed by trial name and country ascending.

ORDER BY is the PostgreSQL term for sorting query results.

Schema

trials

Column	Type	Description
trial_idPK	INTEGER	Unique clinical trial identifier
trial_name	VARCHAR(120)	GSK clinical trial name
phase	VARCHAR(20)	Clinical development phase
therapeutic_area	VARCHAR(80)	Therapeutic area associated with the trial

trial_sites

Column	Type	Description
site_idPK	INTEGER	Unique trial site identifier
trial_id	INTEGER	Trial assigned to the site
country	VARCHAR(60)	Country where the site operates
status	VARCHAR(20)	Operational status of the site

enrollments

Column	Type	Description
enrollment_idPK	INTEGER	Unique enrollment event identifier
site_id	INTEGER	Site associated with the enrollment event
enrolled_on	DATE	Date of the enrollment event
participant_count	INTEGER	Number of participants recorded