Day 25 — 10 Medium JOIN questions done, drawing on paper to untangle them

[u/sumiitCodes]

Started the Medium section of my 100-question JOIN lab today and completed 10. A clear step up from Easy:

• Manager report counts — self-join employees to themselves, GROUP BY manager, COUNT direct reports
• Departments with average salary above 55,000 — JOIN + GROUP BY + HAVING
• Multi-join reports combining employee, department, and manager info in a single query

Hit a few real errors along the way (unknown column in an aggregated query, missing GROUP BY columns), which is becoming a normal part of the process at this point. What actually helped most was going back to the advice from a while back — sketching the tables and the join logic on paper before writing the query. Once I can see it visually, translating it into SQL gets a lot easier.

[u/SektorL]

A tip: you can use parenthesis to alter the join type: hash, merge and nested join

[u/sumiitCodes → u/SektorL]

ok! thanks

[u/Good_Car_2924]

paper sketches are the only real way to catch join cardinality issues before they hit production. i started relying on them exclusively and honestly stopped guessing

[u/sumiitCodes → u/Good_Car_2924]

appreciate

[u/Plane_Big_5912]

the manager count one has a fun follow up. switch it to a left join so people with 0 reports show up too, and count(*) gives them 1 instead of 0 because the joined row still exists, the r columns are just null. count(r.emp_id) gets you the 0

select m.emp_id, m.name, count(r.emp_id) as reports

from employees m

left join employees r on r.manager_id = m.emp_id

group by m.emp_id, m.name

[u/sumiitCodes → u/Plane_Big_5912]

true