-- forked from $DEGEN airdrop details @ https://flipsidecrypto.xyz/edit/queries/933cf3dc-0597-47b7-83fb-c833950d97c9

with toby as (SELECT BLOCK_TIMESTAMP::date as date, sum(AMOUNT_IN) as t,

sum(AMOUNT_OUT) as we, we/t*avg(price) as price_usd ,TOKEN_IN as TOKEN_ADDRESS, symbol_in as symbol

from base.defi.ez_dex_swaps

join crosschain.price.ez_hourly_token_prices on HOUR::date = BLOCK_TIMESTAMP::date

where --tx_hash = '0xee5c5444fa0c317d32e74c518434264d5b563fc7846624d4f2e6fed241f7925f'

HOUR::date >= '2024-01-01'

and BLOCK_TIMESTAMP::date >= '2024-01-01'

and TOKEN_ADDRESS = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

and POOL_NAME = 'WETH-toby 10000 200 UNI-V3 LP'

and TOKEN_IN = '0xb8d98a102b0079b69ffbc760c8d857a31653e56e'

and TOKEN_OUT = '0x4200000000000000000000000000000000000006'

GROUP by all),

berd as ( SELECT BLOCK_TIMESTAMP::date as date, sum(AMOUNT_IN) as t,

sum(AMOUNT_OUT) as we, we/t*avg(price) as price_usd ,TOKEN_IN as TOKEN_ADDRESS, symbol_in as symbol

from base.defi.ez_dex_swaps

join crosschain.price.ez_hourly_token_prices on HOUR::date = BLOCK_TIMESTAMP::date

where --tx_hash = '0xee5c5444fa0c317d32e74c518434264d5b563fc7846624d4f2e6fed241f7925f'

HOUR::date >= '2024-01-01'

and BLOCK_TIMESTAMP::date >= '2024-01-01'

and TOKEN_ADDRESS = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

and POOL_NAME = 'WETH-BERD 10000 200 UNI-V3 LP'

and TOKEN_IN = '0xad1c24de53fad18270d5c99026302e989d212b41'

and TOKEN_OUT = '0x4200000000000000000000000000000000000006'

GROUP by all),

prices as (SELECT HOUR::date as date, avg(price) as price_usd, TOKEN_ADDRESS, SYMBOL as token

from base.price.ez_hourly_token_prices

where HOUR::date >= '2024-01-01' and TOKEN_ADDRESS in ('0x4ed4e862860bed51a9570b96d89af5e1b0efefed',

'0x4229c271c19ca5f319fb67b4bc8a40761a6d6299',