-- use this for origin level tables
-- origin level
SELECT
	region,
	fy_week,
	visits AS visits,
	amount AS amount,
	hours AS hours,
	orders AS orders,
	products) AS products,
	orders / NULLIF(visits::NUMERIC, 0) AS "conversion",
	amount / orders AS dpt,
	products / orders AS upt,
	visits / NULLIF(hours::NUMERIC, 0) AS tphw,
	amount / NULLIF(hours::NUMERIC, 0) AS sph
FROM analytics.finance_r0 -- CHOOSE CORRECT TABLE
WHERE fy_week='2019-W24' -- FISCAL UNIT OF CHOICE
GROUP BY
	region,
	fy_week

UNION

-- region level
SELECT
	region,
	fy_week,
	SUM(visits) AS visits,
	SUM(amount) AS amount,
	SUM(hours) AS hours,
	SUM(orders) AS orders,
	SUM(products) AS products,
	SUM(orders) / NULLIF(SUM(visits::NUMERIC), 0) AS "conversion",
	SUM(amount) / SUM(orders) AS dpt,
	SUM(products) / SUM(orders) AS upt,
	SUM(visits) / NULLIF(SUM(hours::NUMERIC), 0) AS tphw,
	SUM(amount) / NULLIF(SUM(hours::NUMERIC), 0) AS sph
FROM analytics.finance_r0
WHERE fy_week='2019-W24' -- FISCAL UNIT OF CHOICE
GROUP BY
	region,
	fy_week;

UNION

-- province level
SELECT
	zone,
	fy_week,
	SUM(visits) AS visits,
	SUM(amount) AS amount,
	SUM(hours) AS hours,
	SUM(orders) AS orders,
	SUM(products) AS products,
	SUM(orders) / NULLIF(SUM(visits::NUMERIC), 0) AS "conversion",
	SUM(amount) / SUM(orders) AS dpt,
	SUM(products) / SUM(orders) AS upt,
	SUM(visits) / NULLIF(SUM(hours::NUMERIC), 0) AS tphw,
	SUM(amount) / NULLIF(SUM(hours::NUMERIC), 0) AS sph
FROM analytics.finance_r0
WHERE fy_week='2019-W24' -- FISCAL UNIT OF CHOICE
GROUP BY
	zone,
	fy_week;