-- forked from https://flipsidecrypto.xyz/hess/q/WiLcPI3YZDVt/average-price-changes with sei_price as ( Select TO_TIMESTAMP(value[0]::string) as date, 'SEI' as symbol, value[1] as avg_price from ( SELECT livequery.live.udf_api( 'https://api.coingecko.com/api/v3/coins/sei-network/market_chart?vs_currency=usd&days=90&interval=daily&precision=3') as resp ),LATERAL FLATTEN(input => resp:data:prices) UNION SELECT TO_TIMESTAMP(value[0]::string) as date, 'APT' as symbol, value[1] as avg_price FROM ( SELECT livequery.live.udf_api( 'https://api.coingecko.com/api/v3/coins/aptos/market_chart?vs_currency=usd&days=max&interval=daily&precision=3') as resp ) ,LATERAL FLATTEN(input => resp:data:prices) ) , price as (select date, symbol, avg_price from sei_price where date >= current_date() - 30 AND date < current_date() UNION select date(hour) as date, symbol, avg(price) as avg_price from ethereum.price.ez_hourly_token_prices where symbol in ('WETH','WMATIC','WBNB') AND date >= current_date() - 30 AND date < current_date() group by 1,2 UNION select date(hour) as date, symbol, avg(price) as avg_price from avalanche.price.ez_hourly_token_prices where symbol in ('WAVAX') AND date >= current_date() - 30 AND date < current_date() group by 1,2 UNION select date(hour) as date, symbol, avg(price) as avg_price from arbitrum.price.ez_hourly_token_prices where symbol in ('ARB') AND date >= current_date() - 30 AND date < current_date() group by 1,2 UNION select date(hour) as date, symbol, avg(price) as avg_price from optimism.price.ez_hourly_token_prices where symbol in ('OP') AND date >= current_date() - 30 AND date < current_date() group by 1,2 UNION select date(recorded_hour) as date, symbol, avg(price) as avg_price from osmosis.price.ez_prices where symbol in ('OSMO','ATOM') AND date >= current_date() - 30 AND date < current_date() group by 1,2 UNION select date(timestamp) as date, symbol, avg(PRICE_USD) as avg_price from flow.price.fact_prices where symbol = 'FLOW' AND date >= current_date() - 30 AND date < current_date() group by 1,2 UNION select date(timestamp) as date, 'NEAR' as symbol, avg(price_usd) as avg_price from near.price.fact_prices where symbol = 'LINEAR' AND date >= current_date() - 30 AND date < current_date() group by 1,2 UNION select date(recorded_hour) as date, 'SOL' as symbol, avg(close) as avg_price from solana.price.ez_token_prices_hourly where token_address = 'So11111111111111111111111111111111111111112' AND date >= current_date() - 30 AND date < current_date() group by 1,2) , price_2 as (select date, symbol, avg_price from sei_price WHERE date >= current_date() - 30 AND date < current_date() UNION select date(hour) as date, symbol, avg(price) as avg_price from ethereum.price.ez_hourly_token_prices where symbol in ('WETH','WMATIC','WBNB') AND date >= current_date() - 30 AND date < current_date() group by 1,2 UNION select date(hour) as date, symbol, avg(price) as avg_price from avalanche.price.ez_hourly_token_prices where symbol in ('WAVAX') AND date >= current_date() - 30 AND date < current_date() group by 1,2 UNION select date(hour) as date, symbol, avg(price) as avg_price from arbitrum.price.ez_hourly_token_prices where symbol in ('ARB') AND date >= current_date() - 30 AND date < current_date() group by 1,2 UNION select date(hour) as date, symbol, avg(price) as avg_price from optimism.price.ez_hourly_token_prices where symbol in ('OP') AND date >= current_date() - 30 AND date < current_date() group by 1,2 UNION select date(recorded_hour) as date, symbol, avg(price) as avg_price from osmosis.price.ez_prices where symbol in ('OSMO','ATOM') AND date >= current_date() - 30 AND date < current_date() group by 1,2 UNION select date(timestamp) as date, symbol, avg(PRICE_USD) as avg_price from flow.price.fact_prices where symbol = 'FLOW' AND date >= current_date() - 30 AND date < current_date() group by 1,2 UNION select date(timestamp) as date, 'NEAR' as symbol, avg(price_usd) as avg_price from near.price.fact_prices where symbol = 'LINEAR' AND date >= current_date() - 30 AND date < current_date() group by 1,2 UNION select date(recorded_hour) as date, 'SOL' as symbol, avg(close) as avg_price from solana.price.ez_token_prices_hourly where token_address = 'So11111111111111111111111111111111111111112' AND date >= current_date() - 30 AND date < current_date() group by 1,2) , final as (select b.date, case when a.symbol = 'WMATIC' then 'MATIC' when a.symbol = 'WBNB' then 'BNB' when a.symbol = 'WETH' then 'ETH' else a.symbol end as symbols,a.avg_price as selected_price, b.avg_price, (((a.avg_price-b.avg_price)/a.avg_price)*100)*-1 as Price_differ, b.avg_price-a.avg_price as changes from price a join price_2 b on a.symbol = b.symbol ) select date, symbols, case when symbols = 'APT' then 'APT' else 'Other Tokens' end as type, Price_differ as "Price Changes" from final where date = current_date() - 1

Aptos Performance vs. Other Chains (Last 30 days)