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