Magento - New Customer Sales Averages and Totals by Month and Year - direct sql

Magento-new-customer-sales-by-month-year.sql

SELECT sub_query.month_ordered,
       sub_query.year_ordered,
       AVG(sub_query.base_subtotal) AS average_base_subtotal,
       AVG(sub_query.discount_amount) AS average_discount_amt,
       AVG(sub_query.order_qty) AS average_total_item_count,
       COUNT(sub_query.entity_id) AS total_orders
FROM
  (SELECT so.entity_id,
          MONTH(so.created_at) AS month_ordered,
          YEAR(so.created_at) AS year_ordered,
          so.base_subtotal,
          so.discount_amount,
          so.total_item_count AS order_qty,
          so.increment_id
   FROM `sales_flat_order` AS so
   INNER JOIN `customer_entity` AS c ON c.entity_id = so.customer_id
   INNER JOIN `sales_flat_order_item` AS si ON si.order_id=so.entity_id
   WHERE (MONTH(c.created_at) = MONTH(so.created_at)
          AND YEAR(c.created_at) = YEAR(so.created_at))
   GROUP BY entity_id) AS sub_query
GROUP BY sub_query.month_ordered
ORDER BY year_ordered DESC,
         month_ordered DESC