SELECT

TX_ID,

MEMO,

TO_ASSET,

IS_STREAMING,

SWAP_LIMIT_E8,

STREAMING_SWAP_INTERVAL,

STREAMING_SWAP_COUNT,

COUNT(DISTINCT POOL) AS POOL_COUNT,

CASE WHEN MAX(IS_REFUNDED) = 1 THEN TRUE ELSE FALSE END AS IS_REFUNDED,

CASE WHEN MAX(HAD_INBOUND_SWAP) = 1 THEN TRUE ELSE FALSE END AS HAD_INBOUND_SWAP

FROM (

SELECT

TX_ID,

MEMO,

CASE UPPER(SPLIT_PART(MEMO, ':', 2))

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(

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

'-',

1

)

)

END AS TO_ASSET,

CASE