Sales margin

1-- core.unleashed.sales_margin
2-- Revenue, cost of goods and gross margin from completed sales orders, the
3-- numbers behind Unleashed's sales enquiry and gross margin reports.
4-- Grain: completed date x warehouse x customer x product x line type.
5--
6-- Only orders in Completed status count: that is when Unleashed dispatches stock
7-- and fixes the landed cost. Revenue is the line total after discount, before
8-- tax, in base currency. COGS is quantity x the average landed cost at the time
9-- of sale. Charge lines (freight and similar) carry revenue but no cost; keep
10-- line_type in view when reading margin per product.
11--
12-- Credit notes are not netted off here.
13
14SELECT
15    o.completed_date,
16    o.warehouse_id,
17    o.warehouse_name,
18    o.customer_id,
19    o.customer_code,
20    o.customer_name,
21    l.product_id,
22    l.product_code,
23    ANY_VALUE(l.product_description)                                  AS product_description,
24    ANY_VALUE(p.product_group)                                        AS product_group,
25    l.line_type,
26    COUNT(DISTINCT o.sales_order_id)                                  AS orders,
27    SUM(l.quantity)                                                   AS quantity,
28    ROUND(SUM(l.bc_line_total), 2)                                    AS revenue,
29    ROUND(SUM(l.quantity * COALESCE(l.unit_landed_cost, 0)), 2)       AS cogs,
30    ROUND(SUM(l.bc_line_total - l.quantity * COALESCE(l.unit_landed_cost, 0)), 2) AS gross_margin,
31    SAFE_DIVIDE(SUM(l.bc_line_total - l.quantity * COALESCE(l.unit_landed_cost, 0)),
32                SUM(l.bc_line_total))                                 AS gross_margin_pct,
33    COUNTIF(l.unit_landed_cost IS NULL AND l.product_id IS NOT NULL)  AS lines_without_cost
34FROM {{staging.unleashed.sales_order}} o
35JOIN {{staging.unleashed.sales_order_line}} l USING (sales_order_id)
36LEFT JOIN {{staging.unleashed.product}} p ON p.product_id = l.product_id
37WHERE o.order_status = 'Completed'
38GROUP BY o.completed_date, o.warehouse_id, o.warehouse_name, o.customer_id, o.customer_code,
39         o.customer_name, l.product_id, l.product_code, l.line_type