select

max(p.recorded_hour) over() as max_date,

min(p.recorded_hour) over() as min_date,

p.recorded_hour as date,

p.provider,

array_agg(tkn.platform) as platform,

array_agg(tkn.token_address) as token_address,

lower(tkn.name) as name,

tkn.symbol as symbol,

avg(p.close) as closed_price

from crosschain.core.fact_hourly_prices p

inner join crosschain.core.dim_asset_metadata tkn

on p.id = tkn.id

and p.provider = tkn.provider

and p.provider = 'coingecko'

and p.recorded_hour::date between '{{date}}' and current_date()

-- 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 3,4,7,8 ),

base_price AS

(

SELECT

name,

symbol,

closed_price

FROM fact_price_hour

WHERE date = min_date

),

lsd AS

*,

'lsd' as narrative,

concat(name, ' - ', symbol) as id