acquisitions AS()

in super-mode-calculator/src/lib/build-query.ts [33:108]

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

acquisitions AS (
	SELECT
		CASE product
			WHEN 'SUPPORTER_PLUS' THEN
				CASE currency
					WHEN 'GBP' THEN
						CASE payment_frequency
							WHEN 'MONTHLY' THEN 12
							WHEN 'ANNUALLY' THEN 120
							END
					WHEN 'USD' THEN
						CASE payment_frequency
							WHEN 'MONTHLY' THEN 15
							WHEN 'ANNUALLY' THEN 150
							END
					WHEN 'AUD' THEN
						CASE payment_frequency
							WHEN 'MONTHLY' THEN 20
							WHEN 'ANNUAL' THEN 200
							END
					WHEN 'EUR' THEN
						CASE payment_frequency
							WHEN 'MONTHLY' THEN 12
							WHEN 'ANNUALLY' THEN 120
							END
					WHEN 'NZD' THEN
						CASE payment_frequency
							WHEN 'MONTHLY' THEN 20
							WHEN 'ANNUALLY' THEN 200
							END
					WHEN 'CAD' THEN
						CASE payment_frequency
							WHEN 'MONTHLY' THEN 15
							WHEN 'ANNUALLY' THEN 150
							END
					END
			WHEN 'CONTRIBUTION' THEN amount
			WHEN 'RECURRING_CONTRIBUTION' THEN amount
			END AS amount,
		CASE
			WHEN country_Code = 'GB' THEN 'GB'
			WHEN country_Code= 'US' THEN 'US'
			WHEN country_Code = 'AU' THEN 'AU'
			WHEN country_Code = 'NZ' THEN 'NZ'
			WHEN country_Code = 'CA' THEN 'CA'
			WHEN country_Code IN (
			'AD', 'AL', 'AT', 'BA', 'BE', 'BG', 'BL',
			'CH', 'CY', 'CZ', 'DE', 'DK', 'EE', 'ES',
			'FI', 'FO', 'FR', 'GF', 'GL', 'GP', 'GR',
			'HR', 'HU', 'IE', 'IT', 'LI', 'LT', 'LU',
			'LV', 'MC', 'ME', 'MF', 'IS', 'MQ', 'MT',
			'NL', 'NO', 'PF', 'PL', 'PM', 'PT', 'RE',
			'RO', 'RS', 'SE', 'SI', 'SJ', 'SK', 'SM',
			'TF', 'TR', 'WF', 'YT', 'VA', 'AX'
			) THEN 'EU'
			ELSE
			'ROW'
		END AS region,
	product, currency, country_code, referrer_url, payment_frequency,
	FROM datatech-platform-prod.datalake.fact_acquisition_event AS acq
	WHERE event_timestamp >= timestamp '${dateHourString}' AND event_timestamp <  timestamp '${endDateHourString}'),
acquisitions_with_av_gbp AS (
	SELECT acq.*,
		CASE payment_frequency
			WHEN 'ONE_OFF' THEN amount * exch.to_gbp_rate
			WHEN 'MONTHLY' THEN (amount * exch.to_gbp_rate)*12
			WHEN 'ANNUALLY' THEN amount * exch.to_gbp_rate
			END AS av_gbp, exch.from_currency
		FROM acquisitions AS acq JOIN exchange_rates AS exch ON acq.currency = exch.from_currency),
acquisitions_agg AS (
	SELECT region, referrer_url, SUM(av_gbp) sum_av_gbp, COUNT(*) acquisitions
	FROM acquisitions_with_av_gbp
	GROUP BY 1, 2
	),
views_with_regions AS (
		SELECT *,  ${regionSql('country_key')}