select

hour,

case

when TOKEN_ADDRESS = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' then 'ETH'

when TOKEN_ADDRESS = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' then 'USDC'

when TOKEN_ADDRESS = 'So11111111111111111111111111111111111111112' then 'SOL'

when TOKEN_ADDRESS = '7vfCXTUXx5WJV5JADk17DUJ4ksgau7utNKj4b963voxs' then 'tETH'

end as TOKEN_SYMBOL,

price

from crosschain.price.ez_prices_hourly

where TOKEN_ADDRESS in ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2','EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v','So11111111111111111111111111111111111111112','7vfCXTUXx5WJV5JADk17DUJ4ksgau7utNKj4b963voxs')

and BLOCKCHAIN in ('ethereum','solana')

and hour >= '2024-10-20'

qualify row_number() over (partition by token_symbol order by hour desc) = 1),

final as (

(select

block_timestamp,

'Liquidity' as type,

tx_id,

SIGNERS,

mint,

case

when mint = 'Hke78vy1Mzzt5eEJ2jMeKtdqddedDe2rmzjsq16p9ETW' then 'USDC'

when mint = 'So11111111111111111111111111111111111111112' then 'ETH'

when mint = '7mZCsut9beY53V9VWWovrRTBurGv6dozAmuhbwbyHsqk' then 'SOL'

when mint = '8gEs8igcTdyrKzvEQh3oPpZm4HqNYozyczBCPQmZrsyp' then 'tETH'

end as asset,

case when DEST_TOKEN_ACCOUNT in ('MVZFBV7aC73GNedvy3u5geASWGAZkdHtFU16N5Hhddv','29w3f87B9gAkLY3m9rTsob67gvMbg58XsBCTTugMBgfN','9duB6N8ykhwwPZNEBztmLX4erd3dFXzTGGin7v1hNv5r','4oFf8wYGBaMtYko42HiVS6PzRkdA556ky77XVAFMicdH') then amount/pow(10,decimal) end as inbound,

inbound*price as inbound_usd,

case when SOURCE_TOKEN_ACCOUNT in ('MVZFBV7aC73GNedvy3u5geASWGAZkdHtFU16N5Hhddv','29w3f87B9gAkLY3m9rTsob67gvMbg58XsBCTTugMBgfN','9duB6N8ykhwwPZNEBztmLX4erd3dFXzTGGin7v1hNv5r','4oFf8wYGBaMtYko42HiVS6PzRkdA556ky77XVAFMicdH') then amount/pow(10,decimal) end as outbound,

outbound*price as outbound_usd,

case

when c.value = 'Program log: Instruction: Deposit Reserve Liquidity and Obligation Collateral' then 'Deposit'

when c.value = 'Program log: Instruction: Withdraw Obligation Collateral and Redeem Reserve Collateral' then 'Withdraw'