with lst_near_price as (

select top 1

date_trunc(day,HOUR)::date as price_date

,avg(CLOSE) as NEAR_price

from near.price.fact_prices_ohlc_hourly

where ASSET_ID='wrapped-near'

and price_date is not null

and hour::date = current_date-1

group by 1

order by price_date desc

)

, lst_all_meme as (

select

ASSET_ID

,case when ASSET_ID='shitzu' then 'SHITZU'

when ASSET_ID='near-tinker-union-gear' then 'GEAR'

when ASSET_ID='lonk-on-near' then 'LONK'

when ASSET_ID='black-dragon' then 'BLACKDRAGON'

when ASSET_ID='ref-finance' then 'xREF'

when ASSET_ID='neko' then 'NEKO'

end as token_name

,0 as NEAR_price

,date_trunc(day,HOUR)::date as price_date

,avg(CLOSE) as avg_price

from near.price.fact_prices_ohlc_hourly

where ASSET_ID in ('shitzu','near-tinker-union-gear','lonk-on-near','black-dragon','ref-finance','neko')

and price_date is not null

and hour::date = current_date-1

group by 1 ,2 ,3 ,4

order by price_date desc

)

,lst_Other as (

select top 1

symbol_out as ASSET_ID

,ASSET_ID as token_name