Daily sales

1-- core.toast_analytics.daily_sales
2-- Toast's daily sales KPIs per restaurant: orders, guests, gross and net sales,
3-- discounts, refunds, voids, average order value and sales per labor hour.
4-- Grain: business date x restaurant.
5--
6-- The amounts are Toast's own, from its reporting API, so they match the Toast web
7-- reports by construction. The ratios are recomputed from the sums on each row
8-- rather than taken from Toast, so they re-aggregate correctly: to roll up, sum the
9-- numerators and denominators and divide again.
10--
11-- The labor columns here cover hourly jobs only, as Toast reports them in this
12-- endpoint. core.toast_analytics.labor_by_job has all jobs.
13
14SELECT
15    business_date,
16    restaurant_id,
17    restaurant_name,
18    orders,
19    closed_orders,
20    voided_orders,
21    discounted_orders,
22    guests,
23    gross_sales,
24    discounts,
25    net_sales,
26    refunds,
27    voided_amount,
28    SAFE_DIVIDE(net_sales, orders)                  AS avg_order_value,
29    SAFE_DIVIDE(net_sales, guests)                  AS net_sales_per_guest,
30    hourly_labor_hours,
31    hourly_labor_cost,
32    SAFE_DIVIDE(net_sales, hourly_labor_hours)      AS net_sales_per_hourly_labor_hour,
33    SAFE_DIVIDE(hourly_labor_cost, net_sales)       AS hourly_labor_pct_of_net_sales
34FROM {{staging.toast_analytics.aggregated_sales}}