Magento direct SQL query for coupon based orders?

Question

I am trying to write a SQL query for Magento that will show orders which were paid (non-zero total) and those which were coupon based (zero total). The Coupon-based orders should be divided by the promotion that the coupon was from. Then each row of the report show the following:

• The promo name (Shopping cart price rule name)
• Number of orders
• Percentage of total orders
• Average order total

I realize this is could be an extremely complex query, so would be happy with someone pointing out the table(s) that I will need in order to execute this. Any an all help is welcome and thanked =)

Answer 1

This seems to have done the trick

SELECT coupon_rule_name                         AS 'Promotion Used'
     , coupon_code                              AS 'Code Used'
     , COUNT(coupon_code)                       AS 'Times Used / Number of Orders'
     , SUM(subtotal)                            AS 'Cumulative Price'

     , SUM(total_paid)                          AS 'Cumulative Paid with Coupon'
     , AVG(total_paid)                          AS 'Average Order Total (W/  Coupon)'
     , AVG(subtotal)                            AS 'Average Order Total (W/O Coupon)'

     , ABS(SUM(discount_amount))                AS 'Cumulative Savings'

     , (
        SUM(discount_amount) - SUM(total_paid)
       )                                        AS 'Cumulative Loss'

     , CONCAT(ROUND((
        COUNT(coupon_code) / (SELECT COUNT(*) FROM sales_flat_order s)
       ) * 100, 1), '%')                        AS 'Percentage'

FROM     sales_flat_order
WHERE    coupon_code        IS NOT NULL
GROUP BY coupon_code
ORDER BY COUNT(coupon_code) DESC;