MySQL - multiple conditions-CodePudding

I have two tables: orders, shops.

shops:

shop_id | name 
-----------------------
20      | PizzaShop
34      | SushiShop

orders:

orders_id | creation_time       | user_id | shop_id | Status
------------------------------------------------------------------
1         | 2021-01-01 14:00:00 | 1       | 20      | OK
2         | 2021-02-01 14:00:00 | 1       | 34      | Cancelled
3         | 2021-03-01 14:00:00 | 1       | 20      | OK
4         | 2021-04-01 14:00:00 | 1       | 34      | OK
5         | 2021-05-01 14:00:00 | 2       | 20      | OK
6         | 2021-06-01 14:00:00 | 2       | 20      | OK
7         | 2021-07-01 14:00:00 | 2       | 34      | OK
8         | 2021-08-01 14:00:00 | 2       | 34      | OK

I need to find the "favourite" shop of every user, knowing that the favourite is the one that has more "OK" orders, and if there are two shops with the same amount of orders, then select the one that has the most recent order.

The result should be something like this:

user_id | total_number_OK_orders | favourite_shop_name 
------------------------------------------------------------------
1       | 3                      | PizzaShop
2       | 4                      | SushiShop

I have no idea how to do the second column. For the first this is what I have for the moment:

SELECT 
    orders.user_id,
    SUM(if(orders.Status = 'OK', 1, 0)) AS total_number_OK_orders
FROM orders
LEFT JOIN shops
    ON orders.shop_id = shops.shop_id
GROUP BY orders.user_id;

CodePudding user response：

For MySql 8.0 you can use window functions SUM() and FIRST_VALUE():

SELECT DISTINCT o.user_id, 
       SUM(SUM(o.Status = 'OK')) OVER (PARTITION BY o.user_id) total_number_OK_orders,
       FIRST_VALUE(s.name) OVER (PARTITION BY o.user_id ORDER BY SUM(o.Status = 'OK') DESC, MAX(o.creation_time) DESC) favourite_shop_name 
FROM orders o LEFT JOIN shops s
ON s.shop_id = o.shop_id AND o.Status = 'OK'
GROUP BY o.user_id, s.shop_id;

See the demo.

CodePudding user response：

For MySQL 5.7 or earlier

SELECT a.user_id, COUNT(*) total_orders, 
  (
    SELECT MAX(s.name)
    FROM orders t
    JOIN shops s ON t.shop_id = s.shop_id
    WHERE t.user_id = a.user_id AND t.Status = 'OK'
    GROUP BY t.user_id, t.shop_id
    ORDER BY COUNT(*) DESC, MAX(t.creation_time) DESC
    LIMIT 1
  ) favorite_shop
FROM orders a 
LEFT JOIN shops b ON a.shop_id = b.shop_id
WHERE a.Status = 'OK'
GROUP BY a.user_id