How to find best selling products?-CodePudding

I want to find the best selling products according to this criteria.

Minimum order = 5

Product A = 100 orders -last order = 29 Dec 2021
Product B = 6 orders - last order = 1 Jan 2022
Product C = 3 Orders - last order = 3 Jan 2022

Product B must show first.

Product C will not show because it has less than 5 orders.

Here is my database structure and what I tried

http://sqlfiddle.com/#!9/04e2a92/23

CodePudding user response：

Here's the query for getting all products with minimum order count of 5, sorted in descending order.

SELECT p_name, 
    tmp.total_orders, 
    tmp.last_purchased
FROM products P
INNER JOIN (
  SELECT product_id, 
    COUNT(*) AS total_orders, 
    MAX(created_at) AS last_purchased
  FROM order_items
  GROUP BY product_id
  HAVING total_orders >= 5
) AS tmp ON tmp.product_id = P.id
ORDER BY last_purchased DESC

CodePudding user response：

first is to get the products which has been sold for more than 5. then join the resulting table to order_items to get the last date item was sold.

select p.p_name, max(t1.created_at), b.ct from order_items t1
inner join 
  (select  product_id, count(1) as ct from order_items group by product_id having count(1) >= 5) as b
    on b.product_id = t1.product_id
inner join products p on p.id = t1.product_id
group by t1.product_id