with fact_price_hour AS

select

p.hour::date as date,

tkn.symbol as symbol,

p.token_address,

concat(tkn.symbol,p.token_address) as uid,

avg(p.price) as closed_price

from crosschain.core.fact_hourly_prices p

inner join crosschain.core.dim_asset_metadata tkn

on p.token_address = tkn.token_address

and p.provider = tkn.provider

and p.provider = 'coingecko'

and p.hour::date >= '2023-01-01'

-- and tkn.symbol in ('BTC','ETH','USDT','BNB','USDC','XRP','BUSD','ADA','SOL','DOGE','MATIC','DOT','DAI','TRX','SHIB','WBTC','AVAX','UNI','LEO','LTC','LINK','ATOM','ETC','FTT','XLM','CRO','XMR','NEAR','ALGO','QNT','BCH','TON','VET','FLOW','LUNC','FIL','APE','EGLD','HBAR','HT','ICP','XTZ','XCN','APT','AAVE','CHZ','MANA','SAND','EOS','THETA','OKB','USDP','KCS','MKR','BSV','BIT','TUSD','AXS','ZEC','BTT','USDD','XEC','MIOTA','SNX','ETHW','USDN','KLAY','CAKE','NEO','GRT','PAXG','HNT','NEXO','FTM','CSPR','RUNE','GT','LDO','CRV','KAVA','DASH','TWT','ENJ','FEI','BAT','STX','ZIL','DCR','MINA','XDC','COMP','RVN','USTC','1INCH','CVX','WAVES','ENS','AR','XEM','GUSD','LRC','CELO','HOT','BTG','LUNA','ROSE','BNX','KSM','GNO','YFI','QTUM','GMT','BAL','ANKR','TFUEL','GLM','KDA','IOTX','JST','RSR','POLY','GALA','OMG','BTRST','CEL','LPT','XYM','ONE','T','HIVE','ZRX','IOST','RBN','AMP','OP','ICX','CHSB','FLUX','GLMR','BORA','SRM','WOO','ONT','NFT','SUSHI','WAXP','STORJ','XCH','SC','ZEN','MDX','ABBC','SXP','AUDIO','SYN','KNC','UMA','PLA','INJ','SCRT','SLP','SKL','DGB','ELON','ASTR','EWT','RNDR','MXC','IMX','METIS','LSK','PUNDIX','CVC','REEF','DAO','REN','CKB','MED','COTI','VGX','KEEP','REQ','BTCST','CELR','CEEK','ONG','NU','TRIBE','OCEAN','PYR','DYDX','WIN','FXS','SNT','LOOKS','SYS','XNO','ARDR','API3','VLX','HEX','WTRX','stETH','BTCB','FRAX','WBNB','BTTOLD','HBTC','XAUT','MV','OSMO','CCXX','DFI','NXM','GMX','RPL','WEMIX','HUSD','BRISE','FRTS','LUSD','LN','VVS','ZEON','SOLO','DESO','BabyDoge','EURS','LYXe','DEXE','ERG','HUM','vUSDC','USDX','VERI','RKN','UQC','ANY','SSV','WEVER','KRD','EVER','ORBS','MVL','POWR','FX','SAFE','RLC','STEEM','PROM','DAG','AVINOC','WVLX','MX','UOS','PEOPLE','NMR','SPELL','ACA','BICO','MNW','BNT','QUACK','SCHO','CTSI','RACA','CTC','DENT','FUN','CFX','CHR','XPRT','ARRR','PHA','WRX','TEL','XYO','EUROC','STRAX','REP','CFG','BSW','CTK','RAD','TRAC','RAY','OGN','RENBTC','GAL','STPT','QKC','ANT','SUSD','TRR','ORC','ASD','MOB','CBG','STG','ELF')

group by 1,2,3,4

union all

select

hour::date as date,

'AGI' as symbol,

token_address,

'AGI0x5f18ea482ad5cc6bc65803817c99f477043dce85' as uid,

avg(price) as price

from crosschain.core.fact_hourly_prices

where token_address = lower('0x5f18ea482ad5cc6bc65803817c99f477043dce85')

and provider = 'coingecko'

group by 1,2,3,4),

price as

select

*