select

origin_from_address as user, tx_hash, block_timestamp,

sum(event_inputs:amount)/pow(10,18) * avg(price) as volume

from ethereum.core.fact_event_logs l

inner join ethereum.core.fact_hourly_token_prices p on to_char(block_timestamp, 'yyyy-mm-dd HH24:00:00.000 ') = hour

where origin_to_address = lower('0xb8901acB165ed027E32754E0FFe830802919727f')

and event_name = 'TransferSentToL2'

and symbol = 'WETH'

and event_inputs:chainId = '137'

and block_timestamp > CURRENT_DATE - 90

group by 1, 2, 3

order by volume DESC),

next_transactions as (

select ft.tx_hash as tx, from_address, ft.block_timestamp

from ethereum.core.fact_transactions ft

inner join eth_bridges e on e.user = ft.from_address

where from_address in (select user from eth_bridges)

and ft.block_timestamp::date = e.block_timestamp::date

and ft.block_timestamp > e.block_timestamp

order by ft.block_timestamp ASC)

select count(tx_hash) as count, event_name, origin_to_address as contract,

case

when contract = '0x3666f603cc164936c1b87e207f36beba4ac5f18a' then 'USDC'

when contract = '0xb8901acb165ed027e32754e0ffe830802919727f' then 'ETH'

when contract = '0x22b1cbb8d98a01a3b71d034bb899775a76eb1cc2' then 'Matic'

when contract = '0x3e4a3a4796d16c0cd582c382691998f7c06420b6' then 'USDT'

when contract = '0x3d4cc8a61c7528fd86c55cfe061a78dcba48edd1' then 'DAI'

when contract = '0x362fa9d0bca5d19f743db50738345ce2b40ec99f' then 'LIFI Contract'

when contract = '0xc30141b657f4216252dc59af2e7cdb9d8792e1b0' then 'Socket: Registry'

end as route

from ethereum.core.fact_event_logs

where tx_hash in (select tx from next_transactions)

and event_name is not null