WITH latest_prices AS (

SELECT

TRUNC(hour, 'hour') AS truncated_hour,

price

FROM

flow.price.ez_prices_hourly

WHERE

symbol = 'FLOW'

AND price > 0

ORDER BY truncated_hour DESC

LIMIT 1

),

news as (

distinct event_data:initiator as wallet, min(trunc(block_timestamp,'hour')) as debut

from flow.core.fact_events

WHERE

EVENT_CONTRACT = 'A.a45ead1cf1ca9eda.FlowRewards'

AND EVENT_TYPE = 'Locked'

AND TX_SUCCEEDED = 'TRUE'

GROUP by 1

SELECT

TRUNC(BLOCK_TIMESTAMP, 'hour') AS hour,

latest_prices.price,

SUM(CAST(EVENT_DATA:amount AS DECIMAL)) AS "FLOW Locked",

SUM(CAST(EVENT_DATA:amount AS DECIMAL)) * latest_prices.price AS "FLOW Locked (USD)",

sum("FLOW Locked") over (order by hour) as "Total FLOW Locked",

sum("FLOW Locked (USD)") over (order by hour) as "Total FLOW Locked (USD)"

FROM

flow.core.fact_events

left JOIN