Create an operations report that keeps every department—even departments with no courses—while counting its published courses and active enrollments. The joins must not inflate the course count, unpublished courses and cancelled enrollments must not contribute, and the final rows must be ordered by active demand.
Requirements
Keep all supplied tables and seed rows unchanged.
Start from departments so Operations remains visible with zero totals.
Return exactly department, published_courses, and active_enrollments.
Count each published course once even when it has several enrollments.
Count only active enrollments attached to published courses.
Do not count cancelled enrollments or the unpublished Design Draft course.
Group by the department identifier and name.
Sort active_enrollments descending, then department ascending for ties.
Example
Input
Run the supplied three-table schema and seed data, then write one grouped reporting query.
Start with departments and LEFT JOIN outward. Use a conditional DISTINCT count for courses because the enrollment join repeats each course, and a separate SUM(CASE ...) for active enrollments.
tests
RUNTIME OUTPUT
Ready to run the file open above against 1 test.
Runs the open file in a restricted browser worker
LEARN FROM THE SOLUTION
Why the solution works.
The report begins with `departments` because that table defines the complete set of rows the business wants to see. Both relationships use LEFT JOIN, so a department without a matching course still produces one joined row with null course and enrollment values. Starting from courses or using INNER JOIN would silently erase Operations. Joining enrollments creates a second problem: a course appears once per enrollment. `COUNT(DISTINCT CASE WHEN c.published = 1 THEN c.course_id END)` solves both the publication rule and that multiplication effect. The CASE returns a course ID only for published courses, COUNT ignores null values, and DISTINCT ensures each eligible course contributes once. Active enrollment totals have different semantics, so the solution uses `SUM(CASE ...)`: every joined row contributes one only when its course is published and the enrollment status is active; all other rows contribute zero. That excludes cancelled enrollments and also prevents the active enrollment on Design Draft from leaking into the totals. Grouping by both the stable department ID and its display name produces one row per department. The final two-part ordering puts the busiest department first and makes tied totals deterministic. This pattern—preserved parent rows plus conditional aggregates—is central to dashboards where zero activity is still meaningful data.
What this exercise teaches
Preserve unmatched parent rows with LEFT JOIN
Use conditional aggregation for business-specific counts
Prevent one-to-many joins from inflating totals
Group and order a production-style summary report
A PRACTICAL PLAN
Work through Build a complete department enrollment summary with intent.
Translate the contract.Turn the requirements into a short checklist before editing your-solution.sql.
Use the example as evidence.Predict the result for the supplied input, then add one boundary case such as an empty value, a limit, or unexpected input.
Review the implementation.Compare your choices against solution.sql only after a real attempt.
EXERCISE FAQ
Before you move on.
What does “Build a complete department enrollment summary” teach?
This advanced SQL exercise focuses on Preserve unmatched parent rows with LEFT JOIN, Use conditional aggregation for business-specific counts, Prevent one-to-many joins from inflating totals, Group and order a production-style summary report. Its requirements define the exact behavior to implement before you write code.
How should I validate this SQL solution?
Start with the displayed example input and expected output, then test a boundary case suggested by the requirements. Open your-solution.sql and select Run code. The browser creates a fresh isolated SQLite database, executes the complete script, and compares its result rows with the expected report.
When should I open the reference solution?
Attempt Build a complete department enrollment summary first. Then open the read-only solution file to compare the contract, edge-case handling, and implementation choices—not simply to copy the final code.
