acqusitions_with_av AS()

in bandit/src/query-lambda/build-query.ts [83:112]

• 30 lines of code
• 1 McCabe index (conditional complexity)

acqusitions_with_av AS (
  SELECT
    acq.*,
    date,
    CASE payment_frequency
      WHEN 'ONE_OFF' THEN amount * exch.reverse_rate
      WHEN 'MONTHLY' THEN (amount * exch.reverse_rate)*12
      WHEN 'ANNUALLY' THEN amount * exch.reverse_rate
      END
    AS av_eur,
    exch.reverse_rate
  FROM acquisitions AS acq
  JOIN exchange_rates AS exch ON acq.currency = exch.target
),
acqusitions_with_av_gbp AS(
  SELECT
	acq_av.*,
	CASE acq_av.currency
		WHEN 'GBP' THEN av_eur
		ELSE av_eur * (gbp_rate.rate)
		END
	AS av_gbp
	FROM acqusitions_with_av AS acq_av
	JOIN  gbp_rate  ON acq_av.date = gbp_rate.date
	),
acquisitions_agg AS (
  SELECT
    test_name,
    variant_name,
    SUM(IF( av_gbp >= ${ANNUALISED_VALUE_CAP}, ${ANNUALISED_VALUE_CAP},  av_gbp)) sum_av_gbp,