case

when action ilike '%deposit%' then 'Deposit'

when action ilike '%withdraw%' then 'Withdraw'

end as action_type,

*

from

solana.core.fact_stake_pool_actions

succeeded

), daily_pool_actions as (

select

action_type,

STAKE_POOL_NAME,

date_trunc('day', block_timestamp) as day,

count(distinct ADDRESS) as daily_user

-- count(distinct tx_id) as transactions_count,

-- count(distinct stake_pool_name) as pools_count,

-- sum(ifnull(amount, 0)/1e9) as volume

from

pool_actions

group BY

action_type, day, STAKE_POOL_NAME

), daily_flow as (

select

day,

STAKE_POOL_NAME,

daily_user

from

daily_pool_actions

select

day,

STAKE_POOL_NAME,

sum(daily_user) as sum_daily_user,