SELECT livequery.live.udf_api(

'https://api.coingecko.com/api/v3/coins/thorchain/market_chart?vs_currency=usd&days=365&interval=daily&precision=3') as response

,

price_rune as (

SELECT 'THORChain [RUNE]' as ecosystem,

TO_TIMESTAMP(value[0]::string) as date,

value[1] as price,

((price - LAG(price)over(ORDER BY date))/LAG(price)over(ORDER BY date))*100 as daily_change

FROM json , LATERAL FLATTEN (input => response:data:prices)

,

market_cap as (

SELECT

TO_TIMESTAMP(value[0]::string) as date,

value[1] as market_cap_usd

FROM json ,LATERAL FLATTEN (input => response:data:market_caps)

,

trading_volume as (

SELECT

TO_TIMESTAMP(value[0]::string) as date,

value[1] as trading_volume_usd

FROM json,LATERAL FLATTEN (input => response:data:total_volumes)

SELECT

a.date,

price,

IFF(price > LAG(price) over (ORDER BY date), '🟢', IFF(price = LAG(price) over (ORDER BY date), '⚪', '🔴')) as " ",

daily_change,

AVG(price) over (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as moving_avarage_7,

AVG(price) over (ORDER BY date ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) as moving_avarage_30,

market_cap_usd,

trading_volume_usd,