Pirktākās lietas

most_bought.sql

SELECT 
  order_item_name, 
  SUM(meta.meta_value) as total_quantity
FROM wp_woocommerce_order_items items
JOIN wp_woocommerce_order_itemmeta meta ON items.order_item_id = meta.order_item_id
WHERE
  items.order_item_type = 'line_item' 
  AND meta.meta_key = '_qty' 
  AND items.order_id IN (
    SELECT ID FROM wp_posts 
    WHERE post_type = 'shop_order' 
    AND YEAR(post_date) = 2022
  )
GROUP BY order_item_name
ORDER BY total_quantity DESC
LIMIT 100;

SELECT
  order_item_name, 
  SUM(meta.meta_value) as total_quantity
FROM wp_woocommerce_order_items items
JOIN wp_woocommerce_order_itemmeta meta ON items.order_item_id = meta.order_item_id
WHERE
  items.order_item_type = 'line_item' 
  AND meta.meta_key = '_qty'
  AND items.order_item_id NOT IN (
      SELECT order_item_id FROM wp_woocommerce_order_itemmeta
	  WHERE meta_key = '_composite_parent'
  )
  AND items.order_id IN (
    SELECT ID FROM wp_posts 
    WHERE post_type = 'shop_order' 
    AND YEAR(post_date) = 2022
  )
GROUP BY order_item_name
ORDER BY total_quantity DESC
LIMIT 100