Paid vs Coupon sales raw query

paid-vs-coupon.sql

CREATE VIEW foodhub_magento.paid_vs_coupon      AS                      ## One long ass view
SELECT ''                                       AS 'promotion_used'     # 'Promotion Used'
     , ''                                       AS 'promotion_code'     # 'Promotion Code'
     , COUNT(subtotal)                          AS 'times_used'         # 'Times Used (Orders #)'
     , SUM(subtotal)                            AS 'cum_price'          # 'Cumulative Price'
     , ''                                       AS 'cum_paid_coupon'    # 'Cumulative Paid with Coupon'
     , ''                                       AS 'avg_w_coupon'       # 'Average Order Total (W/  Coupon)'
     , AVG(subtotal)                            AS 'avg_wo_coupon'      # 'Average Order Total (W/O Coupon)'
     , ''                                       AS 'cum_saving'         # 'Cumulative Savings'
     , ''                                       AS 'cum_loss'           # 'Cumulative Loss'
     , CONCAT(ABS(ROUND(
        ((COUNT(coupon_code) / (SELECT COUNT(*) FROM foodhubsales_flat_order s) * 100) - 100
       ), 1)), '%')                             AS 'percent'            # 'Percentage'

FROM     foodhubsales_flat_order
WHERE    coupon_rule_name > ''

UNION ALL

SELECT coupon_rule_name                         AS 'promotion_used'     # 'Promotion Used'
     , coupon_code                              AS 'promotion_code'     # 'Promotion Code'
     , COUNT(coupon_code)                       AS 'times_used'         # 'Times Used (Orders #)'
     , SUM(subtotal)                            AS 'cum_price'          # 'Cumulative Price'
     , SUM(total_paid)                          AS 'cum_paid_coupon'    # 'Cumulative Paid with Coupon'
     , AVG(total_paid)                          AS 'avg_w_coupon'       # 'Average Order Total (W/  Coupon)'
     , AVG(subtotal)                            AS 'avg_wo_coupon'      # 'Average Order Total (W/O Coupon)'
     , ABS(SUM(discount_amount))                AS 'cum_saving'         # 'Cumulative Savings'

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

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

FROM     foodhubsales_flat_order
WHERE    coupon_code        IS NOT NULL
GROUP BY coupon_code;

# +----------------------------+----------------+------------+-----------+-----------------+--------------+---------------+------------+-----------+---------+
# | promotion_used             | promotion_code | times_used | cum_price | cum_paid_coupon | avg_w_coupon | avg_wo_coupon | cum_saving | cum_loss  | percent |
# +----------------------------+----------------+------------+-----------+-----------------+--------------+---------------+------------+-----------+---------+
# |                            |                |         10 | 3532.9900 |                 |              |  353.29900000 |            |           | 71.4%   |
# | $201 CREDIT - AMAZON LOCAL | A5NSHHOT44WL   |          1 |  299.9900 | 0.0000          | 0            |  299.99000000 | 299.9900   | -299.9900 | 2.9%    |
# | $201 CREDIT - AMAZON LOCAL | C7556TPKSZG2   |          1 |  400.0000 | 0.0000          | 0            |  400.00000000 | 400.0000   | -400.0000 | 2.9%    |
# | $201 CREDIT - AMAZON LOCAL | DM0XPEEIT26H   |          1 |  400.0000 | 0.0000          | 0            |  400.00000000 | 400.0000   | -400.0000 | 2.9%    |
# | Looting Sale               | FIVE-FINGER    |          3 |  833.0000 | 0.0000          | 0            |  277.66666667 | 833.0000   | -833.0000 | 8.6%    |
# | $201 CREDIT - AMAZON LOCAL | G1CPBI3AM988   |          1 |  400.0000 | 0.0000          | 0            |  400.00000000 | 400.0000   | -400.0000 | 2.9%    |
# | $201 CREDIT - AMAZON LOCAL | JC27EYY43058   |          1 |  400.0000 | 0.0000          | 0            |  400.00000000 | 400.0000   | -400.0000 | 2.9%    |
# | $201 CREDIT - AMAZON LOCAL | WG3HI1BSYTRU   |          1 |  400.0000 | 0.0000          | 0            |  400.00000000 | 400.0000   | -400.0000 | 2.9%    |
# | $201 CREDIT - AMAZON LOCAL | Y7N64X58QRRF   |          1 |  400.0000 | 0.0000          | 0            |  400.00000000 | 400.0000   | -400.0000 | 2.9%    |
# +----------------------------+----------------+------------+-----------+-----------------+--------------+---------------+------------+-----------+---------+