select

tx_id,

block_timestamp,

stake_authority,

stake_account,

event_type,

succeeded,

signer.value::STRING as signer

from solana.gov.ez_staking_lp_actions,

lateral flatten(input => signers) signer

select

date_trunc('day', block_timestamp) as date,

count(distinct tx_id) as total_stakes,

count(distinct case when succeeded = 'true' then tx_id end) as successful_stakes,

count(distinct case when succeeded = 'false' then tx_id end) as failed_stakes,

count(distinct case when event_type = 'delegate' then tx_id end) as delegate_events,

count(distinct case when event_type = 'withdraw' then tx_id end) as withdrawal_events,

count(distinct stake_authority) as total_stakers,

count(distinct stake_account) as stake_accounts,

count(distinct signer) as total_signers

from unnest_signers

where block_timestamp >= '2024-01-01'

and block_timestamp < '2025-01-01'

group by date

order by date asc;