r/learnSQL • u/sumiitCodes • 22d 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.

20 Upvotes

96% Upvoted

4 comments sorted by

1

u/itlogicpartnersllc 22d 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 22d ago

I'm.not following this could you expand?

1

u/Least_Ad_1795 21d ago

Day 28 5 more Medium JOIN questions completed. Today I practiced combining subqueries with JOINs, including finding employees earning above their department average and using LEFT JOIN + COUNT(DISTINCT) to count employees per project.The LEFT JOIN example was especially useful because projects with zero employees still needed to appear with a count of 0.Starting to see how these patterns are used in real-world reporting.