affiliates AS (

SELECT

TRIM(f.value) AS affiliate

FROM LATERAL FLATTEN(INPUT => SPLIT('{{affiliates}}', ',')) AS f

,

date_range AS (

SELECT

(

CASE

WHEN '{{start_date}}' = '' AND '{{end_date}}' = '' THEN DATE_TRUNC('MONTH', CURRENT_DATE)

WHEN '{{start_date}}' = '' THEN DATE_TRUNC('MONTH', '{{end_date}}'::DATE)

ELSE '{{start_date}}'::DATE

END

) AS start_date,

(

CASE

WHEN '{{start_date}}' = '' AND '{{end_date}}' = '' THEN CURRENT_DATE

WHEN '{{end_date}}' = '' THEN DATE_TRUNC('MONTH', '{{start_date}}'::DATE) + INTERVAL '1 MONTH' - INTERVAL '1 DAY'

ELSE '{{end_date}}'::DATE

END

) AS end_date

, attempted_txs AS (

SELECT

TX_ID,

SPLIT_PART(MEMO, ':', 4) LIKE '%/%/%' AS IS_STREAMING,

CASE

WHEN split_part(MEMO, ':', 5) != '' THEN split_part(MEMO, ':', 5)

WHEN (MEMO LIKE '%::0' AND MEMO NOT LIKE '+%') THEN 'te-ios'

ELSE NULL

END AS AFFILIATE_ADDRESS,

CASE UPPER(SPLIT_PART(REPLACE(SPLIT_PART(MEMO, ':', 2), '/', '.'), '-', 1))

WHEN 'A' THEN 'AVAX.AVAX'