WITH 
`total_sales_by_order` AS
        (
        WITH 
        `sales_by_sku` AS 
                (
                SELECT TIMESTAMP_TRUNC(DATETIME(`shopify.refund`.`created_at`,"Asia/Bangkok"), DAY) AS `created_at`, 
                       `shopify.order`.`name` AS `name`,
                       `shopify.order_line`.`sku` AS `sku`,
                       IFNULL(SUM(`shopify.order`.`total_discounts`),0) AS `total_discounts`,
                       IFNULL(SUM(`shopify.order_line`.`price`),0) AS `lineitem_price`,
                       IFNULL(SUM(`shopify.order_line`.`quantity`),0) AS `total_quantity`,
                       IFNULL(SUM(`shopify.order`.`total_price`),0) AS `total_price`,
                       IFNULL(SUM(`shopify.order`.`total_price`),0) + IFNULL(SUM(`shopify.order`.`total_discounts`),0) AS `gross_sales`,
               
                       (
                       IFNULL(SUM(`shopify.order_line`.`quantity`),0) *
                            (IFNULL(SUM(`shopify.order_line`.`price`),0)
                            -
                                (
                               IFNULL(SUM(`shopify.order_line`.`price`),0)
                            / (IFNULL(SUM(`shopify.order`.`total_price`),0) + IFNULL(SUM(`shopify.order`.`total_discounts`),0)))
                               *
                               IFNULL(SUM(`shopify.order`.`total_discounts`),0
                                )
                            )
                       )*-1 AS `lineitem_total_sales`
                 
                  FROM `shopify.order`
          
                LEFT JOIN `shopify.order_line` ON `shopify.order`.`id` = `shopify.order_line`.`order_id`
                LEFT JOIN `shopify.refund` ON `shopify.order`.`id` = `shopify.refund`.`order_id`
                LEFT JOIN `manual_input.date` ON `manual_input.date`.`date` = TIMESTAMP_TRUNC(DATETIME(`shopify.refund`.`created_at`,"Asia/Bangkok"), DAY)
        
                 WHERE `shopify.refund`.`created_at` IS NOT NULL
                GROUP BY `created_at`, `name`, `sku`
         
                UNION ALL
        
                SELECT TIMESTAMP_TRUNC(DATETIME(`shopify.order`.`created_at`,"Asia/Bangkok"), DAY) AS `created_at`, 
                       `shopify.order`.`name` AS `name`,
                       `shopify.order_line`.`sku` AS `sku`,
                       IFNULL(SUM(`shopify.order`.`total_discounts`),0) AS `total_discounts`,
                       IFNULL(SUM(`shopify.order_line`.`price`),0) AS `lineitem_price`,
                       IFNULL(SUM(`shopify.order_line`.`quantity`),0) AS `total_quantity`,
                       IFNULL(SUM(`shopify.order`.`total_price`),0) AS `total_price`,
                       IFNULL(SUM(`shopify.order`.`total_price`),0) + IFNULL(SUM(`shopify.order`.`total_discounts`),0) AS `gross_sales`,
               
                       (
                       IFNULL(SUM(`shopify.order_line`.`quantity`),0) *
                            (IFNULL(SUM(`shopify.order_line`.`price`),0)
                            -
                                (
                                IFNULL(SUM(`shopify.order_line`.`price`),0)
                                    / (IFNULL(SUM(`shopify.order`.`total_price`),0) + IFNULL(SUM(`shopify.order`.`total_discounts`),0)))
                                *
                                IFNULL(SUM(`shopify.order`.`total_discounts`),0
                                )
                            )
                       ) AS `lineitem_total_sales`
                 
                  FROM `shopify.order`
          
                LEFT JOIN `shopify.order_line` ON `shopify.order`.`id` = `shopify.order_line`.`order_id`
                LEFT JOIN `manual_input.date` ON `manual_input.date`.`date` = TIMESTAMP_TRUNC(DATETIME(`shopify.order`.`created_at`,"Asia/Bangkok"), DAY)
                
                GROUP BY `created_at`, `name`, `sku`
                    
                        )
        
        SELECT `sales_by_sku`.`created_at` AS `date`,
                SUM(`sales_by_sku`.`lineitem_total_sales`) AS `total_sales`,
                `sales_by_sku`.`name` AS `order_name`
          FROM `sales_by_sku`
        
        
        LEFT JOIN `manual_input.date` ON `manual_input.date`.`date` = `sales_by_sku`.`created_at`
         WHERE {{date}} AND `sales_by_sku`.`lineitem_total_sales` > 0
         GROUP BY `date`,`order_name`
         ORDER BY `date`, `order_name` DESC        
        )
SELECT 
        CASE
            WHEN `total_sales_by_order`.`total_sales` > 0 AND `total_sales_by_order`.`total_sales` <= 500 THEN "0 - 500"
            WHEN `total_sales_by_order`.`total_sales` > 500 AND `total_sales_by_order`.`total_sales` <= 1000 THEN "0500 - 1000"
            WHEN `total_sales_by_order`.`total_sales` > 1000 AND `total_sales_by_order`.`total_sales` <= 1500 THEN "1000 - 1500"
            WHEN `total_sales_by_order`.`total_sales` > 1500 AND `total_sales_by_order`.`total_sales` <= 2000 THEN "1500 - 2000"
            WHEN `total_sales_by_order`.`total_sales` > 2000 AND `total_sales_by_order`.`total_sales` <= 2500 THEN "2000 - 2500"
            WHEN `total_sales_by_order`.`total_sales` > 2500 AND `total_sales_by_order`.`total_sales` <= 3000 THEN "2500 - 3000"
            WHEN `total_sales_by_order`.`total_sales` > 3000 AND `total_sales_by_order`.`total_sales` <= 4000 THEN "3000 - 4000"
            WHEN `total_sales_by_order`.`total_sales` > 4000 THEN "4000+"
        END AS `aov_bucket`,
        1 AS `number_of_orders`

  FROM `total_sales_by_order`
  ORDER BY `aov_bucket` ASC