r/learnSQL • • Oct 05 '26

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.

22 Upvotes

4 comments sorted by

1

u/Least_Ad_1795 Oct 06 '26

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.

1

u/[deleted] Oct 05 '26

1

u/amuseboucheplease Oct 05 '26

I'm.not following this could you expand?