CODEWITHSUNDEEP
sqlpostgresql
pr338
pr338

Reputation: 9120

How to perform max on an inner join with 2 different counts on columns?

How to find the user with the most referrals that have at least three blue shoes using PostgreSQL?

table 1 - users
name (matches shoes.owner_name)
referred_by (foreign keyed to users.name)

table 2 - shoes
owner_name (matches persons.name)
shoe_name
shoe_color

What I have so far is separate queries returning parts of what I want above:

(SELECT count(*) as shoe_count
FROM shoes
GROUP BY owner_name
WHERE shoe_color = “blue”
AND shoe_count>3) most_shoes

INNER JOIN

(SELECT count(*) as referral_count 
FROM users
GROUP BY referred_by
) most_referrals

ORDER BY referral_count DESC
LIMIT 1

Upvotes: 0

Views: 27

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Two subqueries seem like the way to go. They would look like:

SELECT s.owner_name, s.show_count, r.referral_count
FROM (SELECT owner_name, count(*) as shoe_count
      FROM shoes
      WHERE shoe_color = 'blue'
      GROUP BY owner_name
      HAVING shoe_count >= 3
    ) s JOIN
    (SELECT referred_by, count(*) as referral_count 
     FROM users
     GROUP BY referred_by
    ) r
    ON s.owner_name = r.referred_by
ORDER BY r.referral_count DESC
LIMIT 1 ;

Upvotes: 1

Related Questions