Magento MySQL – The most popular sold product per day

I would like to create “The most popular sold product per day” report  for the last 30 days. The header of the report should look like the picture below. We need the Date, Product SKU number, Product name, Product Brand and Quantity Sold. The most popular product is the product sold the maximum number of times on that day.
Header

 

 
SELECT CreatedAt, sku AS SKU, name AS Description,
 eaov.value AS Brand, MAX( qty_total ) AS Qty
FROM (

SELECT DATE( created_at ) AS CreatedAt, sku,
 SUM( qty_ordered ) AS qty_total, name, product_id
FROM `sales_flat_order_item` AS sfoi
WHERE sfoi.created_at > DATE_SUB( CURDATE( ) , INTERVAL 360 DAY )
GROUP BY sku, CreatedAt
ORDER BY qty_total DESC
) AS item_count
/* get brand name*/
LEFT JOIN `catalog_product_entity_int` AS cpei ON cpei.attribute_id =81
 AND cpei.entity_id = item_count.product_id
LEFT JOIN `eav_attribute_option_value` AS eaov ON eaov.option_id = cpei.value
GROUP BY CreatedAt

Disadvantage: if two product are equally popular, only one of them is chosen for the report.

Leave a Reply

Your email address will not be published. Required fields are marked *