Labor vs sales

1-- core.toast.labor_vs_sales
2-- Labor cost as a share of net sales, and sales per labor hour, per restaurant
3-- and business day.
4-- Grain: business date x restaurant.
5--
6-- Net sales from core.toast.sales_summary, labor from core.toast.labor_by_job
7-- (excluding jobs flagged "exclude from reporting"). To roll up, sum labor_cost,
8-- labor_hours and net_sales first and divide; never average the ratios.
9
10WITH sales AS (
11    SELECT business_date, restaurant_id, ANY_VALUE(restaurant_name) AS restaurant_name,
12           SUM(net_sales) AS net_sales, SUM(orders) AS orders, SUM(guests) AS guests
13    FROM {{core.toast.sales_summary}}
14    GROUP BY 1, 2
15),
16
17labor AS (
18    SELECT business_date, restaurant_id,
19           SUM(total_hours) AS labor_hours, SUM(labor_cost) AS labor_cost
20    FROM {{core.toast.labor_by_job}}
21    WHERE NOT COALESCE(exclude_from_reporting, FALSE)
22    GROUP BY 1, 2
23)
24
25SELECT
26    COALESCE(s.business_date, l.business_date)        AS business_date,
27    COALESCE(s.restaurant_id, l.restaurant_id)        AS restaurant_id,
28    s.restaurant_name,
29    COALESCE(s.orders, 0)                             AS orders,
30    COALESCE(s.guests, 0)                             AS guests,
31    COALESCE(s.net_sales, 0)                          AS net_sales,
32    COALESCE(l.labor_hours, 0)                        AS labor_hours,
33    COALESCE(l.labor_cost, 0)                         AS labor_cost,
34    SAFE_DIVIDE(l.labor_cost, s.net_sales)            AS labor_pct_of_net_sales,
35    SAFE_DIVIDE(s.net_sales, l.labor_hours)           AS net_sales_per_labor_hour
36FROM sales s
37FULL OUTER JOIN labor l USING (business_date, restaurant_id)