Get Top Product Revenue and Order from Virtuemart by MySql Query

This is some query which generate report about various data filter by category, date wise records, day- week -month, order status , number top product  from Virtuemart Joomla component.

1) Get number of order and revenue filter by category, date, order status and weekly monthly or day result

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(o.created_on), '%M %d, %Y') asorder_date, 
FROM_UNIXTIME(UNIX_TIMESTAMP(o.created_on), '%Y%m%d') as date_num,
COUNT(o.virtuemart_order_id) as number_of_orders, 
SUM(o.order_subtotal) as revenue FROM #__virtuemart_orders AS o 
WHERE (UNIX_TIMESTAMP(o.created_on) BETWEEN '1356998400' AND '1385164799' ) 
AND ( o.order_status='C' ) AND o.virtuemart_order_id 
IN ( SELECT p.virtuemart_order_id FROM `#__virtuemart_order_items` as p 
WHERE p.virtuemart_order_id=o.virtuemart_order_id AND p.virtuemart_product_id 
IN(SELECT x.virtuemart_product_id FROM `#__virtuemart_product_categories` as x 
WHERE x.`virtuemart_product_id`=p.`virtuemart_product_id` AND 
( x.virtuemart_category_id=1 ) ) ) 
GROUP BY order_date ORDER BY date_num ASC

2) Get number of order and number of product sale filter by category, date, order status and weekly monthly or day result

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(o.created_on), '%M %d, %Y') as order_date, 
FROM_UNIXTIME(UNIX_TIMESTAMP(o.created_on), '%Y%m%d') as date_num,
SUM(o.product_quantity) as items_sold FROM #__virtuemart_order_items as o 
WHERE (UNIX_TIMESTAMP(o.created_on) 
BETWEEN '1356998400' AND '1385164799' ) AND ( o.order_status='C' 
AND o.virtuemart_order_id 
IN ( SELECT p.virtuemart_order_id FROM `#__virtuemart_order_items` as p 
WHERE p.virtuemart_order_id=o.virtuemart_order_id AND p.virtuemart_product_id 
IN(SELECT x.virtuemart_product_id FROM `#__virtuemart_product_categories` as x 
WHERE x.`virtuemart_product_id`=p.`virtuemart_product_id` 
AND ( x.virtuemart_category_id=1 ) ) ) 
GROUP BY order_date ORDER BY date_num ASC

3) Get number of product item and number of product item sale filter by category, date, order status and weekly monthly or day result

SELECT order_item_name as product_name, order_item_sku as product_sku, 
FROM_UNIXTIME(UNIX_TIMESTAMP(#__virtuemart_order_items.created_on), '%M %d, %Y') 
as order_date, FROM_UNIXTIME(UNIX_TIMESTAMP(#__virtuemart_order_items.created_on), '%Y%m%d') 
as date_num, SUM(product_quantity) as items_sold FROM #__virtuemart_order_items, 
#__virtuemart_orders WHERE (UNIX_TIMESTAMP(#__virtuemart_order_items.created_on) 
BETWEEN '1356998400' AND '1385164799' ) 
AND ( #__virtuemart_order_items.order_status='C' ) 
AND #__virtuemart_orders.virtuemart_order_id=#__virtuemart_order_items.virtuemart_order_id 
AND #__virtuemart_order_items.virtuemart_order_id 
IN ( SELECT p.virtuemart_order_id FROM `#__virtuemart_order_items` as p 
WHERE p.virtuemart_order_id=#__virtuemart_order_items.virtuemart_order_id 
AND p.virtuemart_product_id 
IN(SELECT x.virtuemart_product_id FROM `#__virtuemart_product_categories` as x 
WHERE x.`virtuemart_product_id`=p.`virtuemart_product_id` 
AND ( x.virtuemart_category_id=1 ) ) ) 
GROUP BY product_sku, product_name, order_date ORDER BY date_num, product_name ASC

4) Get top product item and revenue generated of product item sale filter by category, date, order status and weekly monthly or day result

SELECT o.`order_item_name`,o.virtuemart_product_id, 
sum(o.product_quantity) as total_item, 
sum(o.product_quantity)*(o.`product_final_price`) as revenue 
FROM #__virtuemart_order_items as o 
WHERE (UNIX_TIMESTAMP(o.created_on) BETWEEN '1356998400' AND '1385164799' ) 
AND ( o.order_status='C' ) AND o.virtuemart_order_id 
IN ( SELECT p.virtuemart_order_id FROM `#__virtuemart_order_items` as p 
WHERE p.virtuemart_order_id=o.virtuemart_order_id AND p.virtuemart_product_id 
IN(SELECT x.virtuemart_product_id FROM `#__virtuemart_product_categories` as x 
WHERE x.`virtuemart_product_id`=p.`virtuemart_product_id` 
AND ( x.virtuemart_category_id=1 ) ) ) 
GROUP BY o.virtuemart_product_id ORDER BY total_item DESC