SELECT

delegator,

DATE_TRUNC('week', block_timestamp) AS stake_week,

MIN(DATE_TRUNC('week', block_timestamp)) OVER (PARTITION BY delegator) AS first_stake_week

FROM

flow.gov.ez_staking_actions

WHERE

action IN ('TokensCommitted', 'DelegatorTokensCommitted')

AND tx_succeeded = TRUE

),

weekly_staking_data AS (

SELECT

DATE_TRUNC('week', block_timestamp) AS week,

delegator,

COUNT(DISTINCT tx_id) AS weekly_stake_actions,

SUM(amount) AS weekly_stake_volume

FROM

flow.gov.ez_staking_actions

WHERE

tx_succeeded = TRUE

AND block_timestamp >= '2024-08-01'

AND action IN ('TokensCommitted', 'DelegatorTokensCommitted')

GROUP BY

DATE_TRUNC('week', block_timestamp),

delegator

),

categorized_stakers AS (

SELECT

ws.week,

CASE

WHEN ds.stake_week = ds.first_stake_week THEN 'New Staker'

ELSE 'Returning Staker'

END AS staker_type,

COUNT(DISTINCT ws.delegator) AS active_stakers,

SUM(ws.weekly_stake_volume) AS total_stake_volume