SQL: How to select two employeeid within same team and have a team_score greater than 400 ? Mentioned the table schema below

Previous_teams

• id (integer)
• name (text)
• team_score (real)

Team_members

• team_id (integer)
• employee_id (integer)
• employee_name (text)
• birthday (text)
• popularity (real)

Q: How do I select a pair of employee ids that have worked in at least 5 prior teams where the (average)team_score was greater than 400 and count of teams where they worked together. Also avoid any duplicates. Example: team_id pairs of (1,2) need to be taken and not (2,1)

Answer

This is basically a self-join with some aggregation to count the teams that meet the conditions:

select tm1.employee_id, tm2.employee_id
from team_members tm1 inner join
     team_members tm2
     on tm1.team_id = tm2.team_id and
        tm1.employee_id < tm2.employee_id join
     previous_teams pt
     on tm1.team_id = pt.team_id
where pt.team_score > 400
group by tm1.employee_id, tm2.employee_id
having count(*) >= 5;