Sales forecast accuracy

1-- core.fourth.sales_forecast_accuracy
2-- How close Fourth's system forecast and the manager's forecast came to actual
3-- sales, per day, department and sales type. Forecasts drive the rota, so a
4-- forecast that runs high is labour cost spent on empty tables.
5-- Grain: date x department x sales type.
6--
7-- Errors are carried as absolute amounts so they can be summed. Weighted absolute
8-- percentage error over any period is SUM(abs_error) / SUM(actual_sales); bias is
9-- SUM(forecast - actual) / SUM(actual_sales). Days with no actual sales yet
10-- (today, the future) are excluded.
11
12SELECT
13    s.date,
14    dl.location_id,
15    dl.location_name,
16    s.department_id,
17    s.department_name,
18    s.sales_type_id,
19    s.sales_type_name,
20    s.actual_sales,
21    s.system_forecast_sales,
22    s.manager_forecast_sales,
23    s.system_forecast_sales - s.actual_sales                  AS system_forecast_error,
24    ABS(s.system_forecast_sales - s.actual_sales)             AS system_forecast_abs_error,
25    s.manager_forecast_sales - s.actual_sales                 AS manager_forecast_error,
26    ABS(s.manager_forecast_sales - s.actual_sales)            AS manager_forecast_abs_error,
27    s.actual_items,
28    s.system_forecast_items,
29    s.manager_forecast_items
30FROM {{staging.fourth.sales}} s
31LEFT JOIN {{core.fourth.department_location}} dl USING (department_id)
32WHERE s.actual_sales IS NOT NULL
33  AND s.date < CURRENT_DATE()