Hi, I'm having a problem with this query below. Metabase shows it as an error when I use the date filter by the sources inside of the WITH function. How do I get around this?
WITH
`warehouse_mistakes` AS
(
SELECT DATE_TRUNC(`manual_input.cancellations_and_problems`.`date`, DAY) AS `date`,
COUNT(*) AS `warehouse_mistakes`
FROM `manual_input.cancellations_and_problems`
WHERE `manual_input.cancellations_and_problems`.`problem_category` = 'Warehouse Mistake'
GROUP BY `date`
ORDER BY `date` ASC
),
`parcels_shipped` AS
(
SELECT TIMESTAMP_TRUNC(DATETIME(`shopify.order`.`created_at`,"Asia/Bangkok"), DAY) AS `date`,
COUNT(distinct `shopify.order`.`id`) AS `parcels_shipped`
FROM `shopify.order`
LEFT JOIN `shopify.order_line` `Order Line` ON `shopify.order`.`id` = `Order Line`.`order_id`
WHERE `shopify.order`.`fulfillment_status` = 'fulfilled'
GROUP BY `created_at`
ORDER BY `created_at` ASC
)
SELECT SUM(`warehouse_mistakes`.`warehouse_mistakes`) / SUM(`parcels_shipped`.`parcels_shipped`)
FROM `parcels_shipped`
FULL JOIN `warehouse_mistakes` ON `warehouse_mistakes`.`date` = `parcels_shipped`.`date`
WHERE {{date}}