select

date_trunc('day', block_timestamp) as date,

stake_pool_name,

count(distinct tx_id) as tx_count,

count(distinct address) as unique_user,

sum(amount/1e9) as tx_volume,

avg(amount/1e9) as avg_volume

from solana.core.fact_stake_pool_actions

where

date >= current_date - 7 and

action ilike '%deposit%' and

succeeded = 'TRUE'

group by 1, 2

),

dim_date AS (

SELECT dateadd('day', -seq4(), CURRENT_DATE) AS utc_date

FROM TABLE(GENERATOR(rowcount => 10000))

WHERE utc_date BETWEEN '2021-06-18' AND CURRENT_DATE

)

SELECT

d.utc_date,

stake_pool_name,

sum(tx_count) as cum_tx_count,

sum(tx_volume) as cum_tx_volume

FROM pool_name AS v, dim_date AS d

WHERE d.utc_date >= v.date

group by 1, 2