SELECT

Date_trunc('day', Hour) AS Date,

AVG(Price) AS AVG_Price

FROM

flow.price.ez_prices_hourly

WHERE

symbol = 'FLOW'

GROUP BY 1

),

Stake_stats AS (

SELECT

block_timestamp,

delegator AS User,

amount,

amount * avg_price AS amount_usd,

iff(action IN ('DelegatorTokensCommitted', 'TokensCommitted'), 'Stake', 'Unstake') as action

FROM flow.gov.ez_staking_actions

JOIN Flow_Price on block_timestamp::date = date

WHERE tx_succeeded = 1

AND block_timestamp::date >= '2024-01-01'

Date_trunc('day', Block_timestamp) AS Date,

COUNT(DISTINCT User) AS Stakers,

SUM(amount_usd) AS staked_amount_usd

FROM Stake_stats

WHERE action = 'Stake'

GROUP BY 1