How to find best selling products?

Question

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

Answer 1

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