tokens as (

select

ft.value:SYMBOL as symbol,

ft.value:CONTRACT_ADDRESS as contract_address

from (

select

livequery.live.udf_api (

'https://flipsidecrypto.xyz/api/v1/queries/2a812653-c1ee-44b0-9240-3f7d9ef5c0db/data/latest'

):data as data

) lq,

table(flatten(input => lq.data)) ft

),

prices as (

select

hour,

symbol,

price

from

avalanche.price.ez_hourly_token_prices

where

token_address in (select contract_address from tokens)

and hour::date >= (current_date - 31)

)

symbol as "Token",

price as "Price",

lag("Price", 24) over (partition by symbol order by hour) as "24HL",

round(("Price" - "24HL") / "24HL" * 100, 2) as "24H",

case

when "24H" > 0 then 'Positive'

when "24H" < 0 then 'Negative'

end as "24H Change",

lag("Price", 168) over (partition by symbol order by hour) as "7DL",

round(("Price" - "7DL") / "7DL" * 100, 2) as "7D",

case