Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases SQL Reporting by Example Day 3: Getting Good at Grouping Most Popular Teacher

gianca
gianca
6,909 Points
Posted by gianca
gianca
6,909 Points

I think this way is simplest and maybe better.

What's about this ?

SELECT A.ID, FIRST_NAME, LAST_NAME, COUNT(*) AS 'CT' FROM TEACHERS A
JOIN CLASSES B ON B.TEACHER_ID = A.ID
JOIN SCHEDULE C ON C.CLASS_ID = B.ID
JOIN SUBJECTS D ON D.ID = B.SUBJECT_ID
WHERE GRADE IS NULL
GROUP BY A.ID 
ORDER BY CT DESC LIMIT 1
Andrew Phythian
Andrew Phythian
19,747 Points

Or even this...

SELECT first_name, last_name, COUNT(teacher_id) AS Students FROM classes
JOIN subjects ON classes.subject_id = subjects.id
JOIN schedule ON classes.id = schedule.class_id
JOIN teachers ON classes.teacher_id = teachers.id
WHERE grade IS NULL
GROUP BY teacher_id
ORDER BY Students DESC;

The video is using a relatively simple example to illustrate the methodology for solving a more complicated situation.

4 Answers

Stamos Bolovinos
Stamos Bolovinos
4,320 Points
Stamos Bolovinos
Stamos Bolovinos
4,320 Points

I also think the example in the video is to complicated. One CTE would be enough. I used a subquery.

SELECT t.first_name, t.last_name, MAX(ds1.ct) FROM (
  SELECT c.teacher_id, COUNT(1) ct FROM classes c
  JOIN subjects su ON c.subject_id = su.id
  JOIN schedule sc ON c.id = sc.class_id
  WHERE grade IS NULL
  GROUP BY teacher_id) ds1
JOIN teachers t ON ds1.teacher_id = t.id

The subquery could have been written as a CTE for reuseability, agreed, but the teachers example uses 16 line of code for a query that can be written in less than half of that.

Olutope Ayorinde
Olutope Ayorinde
2,875 Points
Olutope Ayorinde
Olutope Ayorinde
2,875 Points

literally was going to post for the first time either one of those solutions because the video seemed so complicated. Glad to know I am not the only one.

sadat sani
sadat sani
4,764 Points
sadat sani
sadat sani
4,764 Points

Some interesting solutions here. But I got thinking: is there a "cleaner" way of writing sql? Or it doesn't matter as long as you get the right answer?

YILEI GE
YILEI GE
2,773 Points
YILEI GE
YILEI GE
2,773 Points

THIS IS THE MOST REAL POST SO FAR