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'

WHEN 'B' THEN 'BTC.BTC'

WHEN 'C' THEN 'BCH.BCH'

WHEN 'N' THEN 'BNB.BNB'

WHEN 'S' THEN 'BSC.BNB'

WHEN 'D' THEN 'DOGE.DOGE'

WHEN 'E' THEN 'ETH.ETH'

WHEN 'G' THEN 'GAIA.ATOM'

WHEN 'L' THEN 'LTC.LTC'

WHEN 'R' THEN 'THOR.RUNE'

ELSE UPPER(

SPLIT_PART(

REPLACE(SPLIT_PART(MEMO, ':', 2), '/', '.'),

'-',

1

)