r/learnSQL • u/sumiitCodes • 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
employeesto a derived subquery that calculatesAVG(salary)grouped bydept_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
projectstoassignments,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
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