WITH api AS (

SELECT

livequery.live.udf_api('https://api.llama.fi/protocol/aave') AS response

limit 100

),

flattened_data AS (

SELECT

IFNULL(VALUE:"totalLiquidityUSD", 0) AS tvl,

TO_VARCHAR(TO_TIMESTAMP(VALUE:"date"), 'YYYY-MM-DD') AS date

FROM

api,

LATERAL FLATTEN(response:data:chainTvls:Avalanche:tvl)

),

dayly_data AS (

SELECT

DATE_TRUNC('day', TO_DATE(date, 'YYYY-MM-DD')) AS day,

MAX(tvl) AS dayly_tvl

FROM

flattened_data

WHERE

date >= '2021-01-01'

GROUP BY

day

),

dayly_change AS (

SELECT

day,

dayly_tvl,

LAG(dayly_tvl, 1) OVER (

ORDER BY

day

) AS previous_day_tvl

FROM

dayly_data