Menu item sales

1-- core.toast.menu_item_sales
2-- Toast's Product Mix: quantity, gross and net sales and voids per menu item.
3-- Grain: business date x restaurant x menu item x sales category.
4--
5-- Uses the same rules as core.toast.sales_summary (voided and deleted orders and
6-- checks out, deferred selections out), so net_sales summed over items equals
7-- net_sales in the sales summary for the same day and restaurant. Voided
8-- selections are not in the sales columns; they are counted in voided_quantity
9-- and voided_amount.
10--
11-- item_name is the name printed on the check. An item renamed in the menu shows
12-- under its old name on old orders; group by menu_item_id to keep them together.
13
14WITH valid_checks AS (
15    SELECT c.check_id, o.business_date, o.restaurant_id
16    FROM {{staging.toast.check}} c
17    JOIN {{staging.toast.order}} o USING (order_id)
18    WHERE NOT o.is_voided AND NOT o.is_deleted
19      AND NOT c.is_voided AND NOT c.is_deleted
20)
21
22SELECT
23    vc.business_date,
24    vc.restaurant_id,
25    s.menu_item_id,
26    ANY_VALUE(s.item_name)                                         AS item_name,
27    s.sales_category_id,
28    ANY_VALUE(sc.sales_category_name)                              AS sales_category_name,
29    SUM(IF(NOT s.is_voided, s.quantity, 0))                        AS quantity_sold,
30    ROUND(SUM(IF(NOT s.is_voided, s.pre_discount_price, 0)), 2)    AS gross_sales,
31    ROUND(SUM(IF(NOT s.is_voided, s.pre_discount_price - s.price, 0)), 2) AS discounts,
32    ROUND(SUM(IF(NOT s.is_voided, s.price, 0)), 2)                 AS net_sales,
33    ROUND(SUM(IF(NOT s.is_voided, s.refund_amount, 0)), 2)         AS refunds,
34    SUM(IF(s.is_voided, s.quantity, 0))                            AS voided_quantity,
35    ROUND(SUM(IF(s.is_voided, s.pre_discount_price, 0)), 2)        AS voided_amount,
36    COUNT(DISTINCT IF(NOT s.is_voided, s.check_id, NULL))          AS checks_with_item
37FROM {{staging.toast.selection}} s
38JOIN valid_checks vc USING (check_id)
39LEFT JOIN {{staging.toast.sales_category}} sc
40       ON sc.sales_category_id = s.sales_category_id AND sc.restaurant_id = vc.restaurant_id