with json as (

SELECT livequery.live.udf_api(

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

,

price_rune as (

SELECT 'Near [near]' 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 as "Date",

case when month(a.date) <= 3 then 'Q1'

when month(a.date) between 3 and 6 then 'Q2'

when month(a.date) between 6 and 9 then 'Q3'