(select *

from bsc.core.fact_event_logs

where origin_to_address ilike '0xdef171fe48cf0115b1d80b88dc8eab59176fee57'

and event_name = 'Swap'

),

base4 as

(select tx_hash,

contract_address,

address_name,

upper(address_name) as address_names,

split(split(address_names, ':')[1], ' ')[1] as token_name

from bsc.core.fact_event_logs a

left join bsc.core.dim_labels b

on a.contract_address = b.address

where

event_name = 'Transfer'

and tx_hash in (select tx_hash from base3)

and event_inputs:value > 0

and address_name is not null

and block_timestamp::date >= '2022-07-01'

),

base5 as (select tx_hash,

array_agg(distinct(token_name)) within group (order by token_name asc) as pairing_swapped,

1 as n

from base4

group by 1)

select pairing_swapped,

ifnull(concat(pairing_swapped[0], '->', pairing_swapped[1], '->', pairing_swapped[2]), concat(pairing_swapped[0], '->', pairing_swapped[1])) as asset_pair,

from base5

where asset_pair is not null

group by 1