Common SQL Query Patterns

A classic pattern: highest score per course, latest enrollment per student. Often uses ROW_NUMBER() or GROUP BY + MAX.

Top N per group

A classic pattern: highest score per course, latest enrollment per student. Often uses ROW_NUMBER() or GROUP BY + MAX.

Real-life example: Top N per group is picking the class captain from each grade — one winner per grade, not one for the whole school.

Highest score per course (simple GROUP BY)

SELECT e.course_id, MAX(e.score) AS top_score
FROM enrollments e
GROUP BY e.course_id;

Existence checks — who has / who lacks

Use LEFT JOIN ... WHERE right.id IS NULL or NOT EXISTS to find students with no enrollments.

Real-life example: Finding who lacks enrollment is the list of students who never signed up for any club.

Students with no enrollments

SELECT s.student_id, s.full_name
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
WHERE e.enrollment_id IS NULL;

Running totals and rankings

Window functions (where supported) compute rank and running sum without collapsing rows.

Real-life example: Running total is a savings passbook — each line adds to the balance so far.

Rank scores within each course

SELECT
  student_id,
  course_id,
  score,
  RANK() OVER (PARTITION BY course_id ORDER BY score DESC) AS rank_in_course
FROM enrollments
WHERE score IS NOT NULL;

Conditional aggregation

SUM(CASE WHEN ... THEN 1 ELSE 0 END) counts rows that meet a condition inside one query.

Real-life example: Conditional aggregation is one survey sheet counting yes/no answers in a single table scan.

Count honors vs others per course

SELECT
  course_id,
  SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END) AS honor_count,
  SUM(CASE WHEN score < 90 THEN 1 ELSE 0 END) AS other_count
FROM enrollments
GROUP BY course_id;