Labour vs sales

1-- core.fourth.labour_vs_sales
2-- Labour cost as a share of sales, and sales per labour hour, per day and
3-- department. The headline wage-percentage view in Fourth.
4-- Grain: date x department.
5--
6-- Sales are summed over every sales type; labour over every payroll line. If
7-- your payroll carries lines that should not count towards wage cost (see
8-- core.fourth.labour_cost_daily for the types, pay parts and statuses present),
9-- filter them in the labour CTE below.
10--
11-- Ratios are computed per row. To roll up to a week, a location or the estate,
12-- sum labour_cost and actual_sales first and divide, never average the
13-- percentages.
14
15WITH sales AS (
16    SELECT
17        date,
18        department_id,
19        ANY_VALUE(department_name)        AS department_name,
20        SUM(actual_sales)                 AS actual_sales,
21        SUM(system_forecast_sales)        AS system_forecast_sales,
22        SUM(manager_forecast_sales)       AS manager_forecast_sales
23    FROM {{staging.fourth.sales}}
24    GROUP BY 1, 2
25),
26
27labour AS (
28    SELECT
29        date,
30        department_id,
31        ANY_VALUE(department_name)        AS department_name,
32        SUM(hours)                        AS labour_hours,
33        SUM(pay_value)                    AS labour_cost
34    FROM {{staging.fourth.payroll}}
35    GROUP BY 1, 2
36)
37
38SELECT
39    COALESCE(s.date, l.date)                                  AS date,
40    dl.location_id,