select

date_trunc('day', hour) as date,token_address,

avg(price) as price

from

crosschain.core.fact_hourly_prices

WHERE

IS_IMPUTED = 'false'

and PROVIDER in ('coinmarketcap', 'coingecko')

group by

1,2

),

all_tx as (

SELECT

tx_hash,

block_timestamp::date as date,

ORIGIN_FROM_ADDRESS as wallet_address,

case

when AMOUNT_IN_USD is not null then AMOUNT_IN_USD

when amount_out_usd is not null and AMOUNT_IN_USD is null then amount_out_usd

end as amount_usd

FROM

ethereum.core.ez_dex_swaps ez inner join price_tl pr_in on ez.TOKEN_IN=pr_in.token_address and pr_in.date=ez.block_timestamp::date

inner join price_tl pr_out on ez.TOKEN_out=pr_out.token_address and pr_out.date=ez.block_timestamp::date

WHERE

ORIGIN_TO_ADDRESS = '0x881d40237659c251811cec9c364ef91dc08d300c'

and event_name like '%Swap%'

and BLOCK_NUMBER>=11042542

),

all_user as (

SELECT

wallet_address,

rank() over (

ORDER BY

sum(AMOUNT_USD) desc