SELECT

date_trunc('hour', hour) AS hour,

'WKLAY' AS symbol,

'0x57d1a61e4fd09fbf16e35b04959e94dcf2032974' AS token_address,

FIRST_VALUE(price) OVER (PARTITION BY date_trunc('hour', hour) ORDER BY hour) AS open,

MAX(price) OVER (PARTITION BY date_trunc('hour', hour)) AS high,

MIN(price) OVER (PARTITION BY date_trunc('hour', hour)) AS low,

LAST_VALUE(price) OVER (PARTITION BY date_trunc('hour', hour) ORDER BY hour RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS close

FROM

kaia.price.ez_prices_hourly

WHERE

symbol = 'KLAY'

AND hour::date >= current_date - interval '1 year'

ORDER BY

hour

),

dim_label AS (

SELECT

log.value:ADDRESS AS address

,log.value:CATEGORY AS category

,log.value:PROJECT_NAME AS project_name

,log.value:LABEL_1 AS label

FROM

(SELECT LiveQuery.live.udf_api(

'https://flipsidecrypto.xyz/api/v1/queries/fbe49238-ba79-4d5c-820f-bac6b8b5b215/data/latest'

)AS resp

) re

INNER JOIN LATERAL FLATTEN (input => re.resp:data) log

WHERE project_name = 'Frog Defense'

),

base AS (