(

with prices_table as

(

select hour::date as "date",

token_address, symbol,avg(decimals) as decimals, avg(price) as avg_price from ethereum.core.fact_hourly_token_prices

-- where token_address=lower('0x7fc66500c84a76ad7e9c93437bfc5ac33e2ddae9')

group by "date", token_address,symbol

)

-- distinct contract_address

ftt.block_timestamp::date as "date",

ftt.tx_hash,

ftt.raw_amount,

ftt.from_address,

ftt.to_address,

pt.decimals,

pt.avg_price,

ftt.raw_amount/pow(10,pt.decimals) as amount,

pt.symbol,

amount*avg_price as amount_usd,

case when symbol='USDC' and amount in (4000,1200,600,300) then 'contributor'

when symbol='USDC' and amount between 4010 and 3990 then 'contributor'

when symbol='USDC' and amount > 10000 then 'curation pod'

else 'analyst'

end as user

ethereum.core.fact_token_transfers ftt inner join

prices_table pt

on pt."date"=ftt.block_timestamp::DATE

and pt.token_address=ftt.contract_address

where 1=1

and ftt.from_address = lower('0x4faFB87de15cFf7448bD0658112F4e4B0d53332c')

)

select *,date_trunc('month',"date") as month from inn