Capstone: Reporting Queries
Write queries that aggregate enrollments, calculate course popularity, and show a user's progress. Ensure counts reflect the intended grain and that outer joins preserve courses with zero enrollments when needed. Add deterministic ordering and pagination for user-facing lists.
Test queries with edge cases: empty tables, missing relationships, duplicate-looking values, NULLs, and large result sets. A query that works for one sample row may still be logically wrong or slow at scale.
Courses including zero enrollments
SELECT c.id, c.title, COUNT(e.user_id) AS enrollment_count
FROM courses AS c
LEFT JOIN enrollments AS e ON e.course_id = c.id
GROUP BY c.id, c.title
ORDER BY enrollment_count DESC, c.id
LIMIT 20;💡 The LEFT JOIN keeps courses with no enrollments. COUNT(e.user_id) returns zero for unmatched courses because the joined user_id is NULL.
Key Takeaways
- Use LEFT JOIN when zero-related-row entities must remain in results.
- COUNT(column) excludes NULL, allowing zero counts for unmatched rows.
- Test empty and edge-case data, not only happy paths.
Knowledge Check
Why use COUNT(e.user_id) instead of COUNT(*) in this LEFT JOIN aggregation?