202303_solution.sql

with
trxs as (
	select
		case "Online or In-Person"
			when 1 then 'Online'
			when 2 then 'In-Person'
		end as online_or_in_person,
		date_part('quarter', strptime(left("Transaction Date", 10), '%d/%m/%Y')) as quarter,
		sum("Value") as value
	from {{ ref("src_week_202303_transactions") }}
	where string_split("Transaction Code", '-')[1] = 'DSB'
	group by 1, 2
),

trgt as (
	{%- for col in ["Q1", "Q2", "Q3", "Q4"] -%}
    select
      "Online or In-Person" as online_or_in_person,
      cast(right('{{ col }}', 1) as int) as quarter,
      {{ col }} as quarterly_targets
    from {{ ref("src_week_202303_targets") }}

    {% if not loop.last -%}
      union all
    {% endif -%}
  {%- endfor -%}
)

select
	trxs.online_or_in_person,
	trxs.quarter,
	trxs.value,
	trgt.quarterly_targets,
	trxs.value - trgt.quarterly_targets as variance_to_target
from trxs
left join trgt
	on trxs.online_or_in_person = trgt.online_or_in_person
		and trxs.quarter = trgt.quarter