with bridgoor as (

select

case when contract_address='0x50b7545627a5162f82a992c33b87adc75187b218' then event_inputs:value*pow(10,-8)

when contract_address='0xa7d7079b0fead91f3e65f86e8915cb59c1a4c664' then event_inputs:value*pow(10,-6)

when contract_address='0xc7198437980c041c805a1edcba50c1ce5db95118' then event_inputs:value*pow(10,-6)

else event_inputs:value*pow(10,-18) end as amount,

event_inputs:to as user, contract_address as token, block_timestamp as bridge_time

-- ,row_number() over (partition by user order by block_timestamp desc) as row_num

from avalanche.core.fact_event_logs

where origin_from_address='0xeb1bb70123b2f43419d070d7fde5618971cc2f8f' and event_name is not null and block_timestamp <='2022-08-25 16:00:46.000' and block_timestamp >= '2022-08-01 00:00:00.000'

),

bridge2 as (

select sum(amount) as total_amount, user, token, max(bridge_time) as latest_tx

from bridgoor

group by user, token

),

purchases as (

-- tokenId, price, buyer

select event_inputs:tokenId as tokenId, event_inputs:price*pow(10,-18) as buy_price,

case when event_name='TakerBid' then event_inputs:taker

when event_name='TakerAsk' then event_inputs:maker

else 'na' end as buy_user, tx_hash

from avalanche.core.fact_event_logs

where event_name in ('TakerBid','TakerAsk') and event_inputs:collection=lower('0xB842344669579ECf4Cee12f740520376c4CBC6d1') and contract_address='0xae079eda901f7727d0715aff8f82ba8295719977'

),

sales as (

-- tokenId, price, seller

select event_inputs:tokenId as tokenId, event_inputs:price*pow(10,-18) as sell_price,

case when event_name='TakerBid' then event_inputs:maker

when event_name='TakerAsk' then event_inputs:taker

else 'na' end as sell_user,

tx_hash

from avalanche.core.fact_event_logs

where event_name in ('TakerBid','TakerAsk') and event_inputs:collection=lower('0xB842344669579ECf4Cee12f740520376c4CBC6d1') and contract_address='0xae079eda901f7727d0715aff8f82ba8295719977'

),