Sales summary

1-- core.toast.sales_summary
2-- Toast's Sales Summary: orders, guests, gross and net sales, discounts, tax,
3-- service charges, tips and refunds.
4-- Grain: business date x restaurant x revenue center x dining option.
5--
6-- Every amount is first summed to the order, then grouped, so the three money
7-- sources (selections, service charges, payments) never multiply each other.
8--
9-- What counts:
10--   orders and checks that are neither voided nor deleted
11--   selections that are not voided; deferred selections (gift card sales) are
12--     kept out of sales and reported as deferred_sales
13--   payments that were not voided, denied, cancelled or in error
14-- gross_sales is the sum of pre-discount prices, net_sales the sum of prices after
15-- discounts, discounts the difference. Refunds are reported separately and are not
16-- deducted from net_sales; net_sales_after_refunds does that.
17-- check_amount is Toast's own check total before tax, kept as a cross-check: it
18-- should be close to net_sales + service_charges + gratuities.
19
20WITH orders AS (
21    SELECT *
22    FROM {{staging.toast.order}}
23    WHERE NOT is_voided AND NOT is_deleted
24),
25
26checks AS (
27    SELECT c.*
28    FROM {{staging.toast.check}} c
29    JOIN orders o USING (order_id)
30    WHERE NOT c.is_voided AND NOT c.is_deleted
31),
32
33selection_totals AS (
34    SELECT
35        s.order_id,
36        SUM(IF(NOT s.is_deferred, s.quantity, 0))             AS items_sold,
37        SUM(IF(NOT s.is_deferred, s.pre_discount_price, 0))   AS gross_sales,
38        SUM(IF(NOT s.is_deferred, s.price, 0))                AS net_sales,
39        SUM(IF(s.is_deferred, s.price, 0))                    AS deferred_sales,
40        SUM(s.tax)                                            AS tax,