select

RECORDED_AT::date as days , SYMBOL,

avg(price) as prices

from

osmosis.core.dim_prices

where provider = 'coinmarketcap'

group by 1 ,2

,base as (

select

l.*, PROJECT_NAME as LABEL

from

osmosis.core.fact_transfers l , osmosis.core.dim_labels b

where CURRENCY = b.ADDRESS

)

,fin as (

select

b.*,prices,amount/pow(10,decimal) as AMOUNTs ,

AMOUNTs*prices as amount_usd,

row_number()over(partition by receiver order by BLOCK_TIMESTAMP asc) as n

from

base b,prices p

where TRANSFER_TYPE = 'IBC_TRANSFER_IN'

and

BLOCK_TIMESTAMP::date = days and LABEL = SYMBOL

select

date_trunc(week,BLOCK_TIMESTAMP)::date as date,

label,