'How do I to Identify top 3 customers in Postgresql?

I'm trying to identify the top three customers (owner_id) based upon how many visits they've had to a dog_salon business.

Some owners have more than one pet, so I want to add up all of their visits across all pets. The top three customers (owner_id) should be listed in descending order according to number of visits (use the alias num_visits)! I have four tables owners, owners_2, transactions, and visits.

  • owners has 3 columns. owner_id, pet_id, and size (owner_id is different in owners and owners_2 table)
  • owners_2 has 3 columns. owner_id, pet_id, and size
  • transactions has 4 columns. transaction_id, date, pet_id, and service
  • visits has 2 columns. pet_id and visits_count

This is the formula I tried to no luck.

SELECT
    owners.owner_id, owners_2.owner_id, owners.pet_id, owners_2.pet_id, 
    COUNT(visits.visits_count) AS num_visits 
FROM 
    owners 
INNER JOIN 
    owners_2 ON owners.pet_id = owner_2.pet_id 
INNER JOIN 
    visits ON visits.pet_id = owners_2.pet_id 
GROUP BY 
    owners.owner_id, owners_2.owner_id, owners.pet_id, owners_2.pet_id 
ORDER BY 
    visits.visits_count 
DESC LIMIT 3
postgresqljoin


Solution 1:[1]

If our owners and owners_2 are just two owners table containing different owners, you can union all them before joining to the visits table like in the following

with all_owners as (
select owner_id, pet_id from owners union all
select owner_id, pet_id from owners_2)

select owners.owner_id, sum(visits.visits_count) AS num_visits
from all_owners owners INNER JOIN visits on visits.pet_id=owners.pet_id
group by owners.owner_id
ORDER BY 2 desc desc
limit 3

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ftisiot

Related Questions