CODEWITHSUNDEEP
mysql
Somjit
Somjit

Reputation: 2772

a better way to get customers who placed maximum and minimum number of orders

My question is to find all those customers who have made the minimum and maximum no of orders.

what i could come up with is this :

select customer_id , count(order_id) as num 
from bab_customer right outer join bab_order_details using(customer_id)
group by customer_id
having count(order_id) >= 
    all(select count(order_id) 
        from bab_customer right outer join bab_order_details using(customer_id)
        group by customer_id)
or count(order_id) <= 
    all(select count(order_id) 
        from bab_customer right outer join bab_order_details using(customer_id)
        group by customer_id);

it gives me the correct output , but i have to do the same join 3 times. is there some better way to do this ?

Upvotes: 0

Views: 660

Answers (3)

user645280
user645280

Reputation:

If I absolutely felt this must be a single query, I'd probably just use a union all. 

(
select
  customer_id,
  count(*) as num
from bab_customer right outer join bab_order_details using(customer_id)
group by customer_id
order by num desc limit 1
)
union all
(
select
  customer_id,
  count(*) as num
from bab_customer right outer join bab_order_details using(customer_id)
group by customer_id
order by num asc limit 1
)

Originally, I'd been thinking this would perform better due to avoiding joins on intermediate values. However, I'm not seeing any performance gain here. (If it was faster, then limit could be raised to some arbitrary number and the application could trim out a few extra values without losing much of the increased performance)

But, no dice.

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

Is this better? I don't know...

SELECT x.* 
  FROM 
     ( SELECT customer_id
            , COUNT(*) cnt 
         FROM orders 
        GROUP  
           BY customer_id
     ) x 
  JOIN 
     ( SELECT MIN(cnt) min_cnt
            , MAX(cnt) max_cnt 
         FROM 
            ( SELECT customer_id
                   , COUNT(*) cnt 
                FROM orders 
               GROUP 
                  BY customer_id
            ) n
     ) y 
    ON y.min_cnt = x.cnt 
    OR y.max_cnt = x.cnt;

Upvotes: 2

ScayTrase
ScayTrase

Reputation: 1830

I realised, that you cannot run it in a single query without calling same subqueries several times. As you refer min(numOrders) and max(numOrders) you have to store numOrders as a temporal table or to calculate it twice. Most likely the DBMS will cache your requests if they are identical, so you should not worry about wasting resources.

So your query must be a bit rewrited and is good enough. My variant is

select customer_id , count(order_id) as num 
from customers right outer join orders using(customer_id)
group by customer_id
having 
  num = (SELECT min(num) FROM (select count(order_id) as num 
    from customers right outer join orders using(customer_id)
    group by customer_id) numOrders)
or  
  num = (SELECT max(num) FROM (select count(order_id) as num 
    from customers right outer join orders using(customer_id)
    group by customer_id) numOrders)

You can check it working with this sqlfiddle

Upvotes: 1

Related Questions