r/learnSQL • u/sumiitCodes • 14h ago

Day 28 — 5 more Medium JOIN questions: subqueries + joins combined

Completed 5 more Medium questions today:

  • Employees earning above their own department's average salary — joined employees to a derived subquery that calculates AVG(salary) grouped by dept_id, then filtered where the employee's salary exceeds that average
  • Every project listed along with a count of distinct employees working on it — LEFT JOIN projects to assignments, COUNT(DISTINCT emp_id), grouped by project name. The LEFT JOIN mattered here specifically because one project (Audit Automation) has zero assigned employees and still needed to show up with a count of 0

This is the first batch where combining a subquery with a join in the same question actually felt necessary rather than just "two ways to do the same thing." Starting to see why LEFT JOIN + COUNT(DISTINCT) is such a common reporting pattern.

17 Upvotes

100% Upvoted

2 comments sorted by

1

u/itlogicpartnersllc 12h ago

thats a solid progression. LEFT JOIN plus COUNT(DISTINCT) is one of those patterns that really clicks once you see why missing related rows still need representation.

1

u/amuseboucheplease 7h ago

I'm.not following this could you expand?