tc_swaps as (

select distinct

first_value(lower(from_address)) over (partition by tx_id order by block_timestamp) as from_address,

last_value(lower(native_to_address)) over (partition by tx_id order by block_timestamp) as to_address,

case

when to_address = from_address and rlike(from_address,'0x[a-z0-9]{40}') then from_address

when to_address <> from_address and rlike(from_address,'0x[a-z0-9]{40}') then from_address

when to_address <> from_address and rlike(to_address,'0x[a-z0-9]{40}') then to_address

end as swapper, -- ETH swapper

'THORChain' as swap_program,

block_timestamp,

tx_id as tx_hash,

-- first_value(blockchain) over (partition by tx_id order by block_timestamp, min_to_amount) as first_bc,

-- last_value(blockchain) over (partition by tx_id order by block_timestamp, min_to_amount) as last_bc,

first_value(from_asset) over (partition by tx_id order by block_timestamp, min_to_amount) as symbol_in,

first_value(from_amount) over (partition by tx_id order by block_timestamp, min_to_amount) as amount_in,

first_value(from_amount_usd) over (partition by tx_id order by block_timestamp, min_to_amount) as amount_in_usd,

last_value(to_asset) over (partition by tx_id order by block_timestamp, min_to_amount) as symbol_out,

last_value(to_amount) over (partition by tx_id order by block_timestamp, min_to_amount) as amount_out,

last_value(to_amount_usd) over (partition by tx_id order by block_timestamp, min_to_amount) as amount_out_usd

from flipside_prod_db.thorchain.swaps

where datediff('month',block_timestamp,current_date()) <= 3

and blockchain = 'ETH'

qualify (rlike(lower(from_address),'0x[a-z0-9]{40}')

or rlike(lower(to_address),'0x[a-z0-9]{40}'))

),