Query editor
withstake_accounts as (SELECT account_stakeAccount as stake FROM stake_program_solana.stake_call_InitializeUNIONSELECT account_stakeAccount as stake FROM stake_program_solana.stake_call_InitializeCheckedUNIONSELECTcase when action = 'withdraw' then source --if withdraw, then stake account is the withdraw account.else destination --if merge or split, then both are stake accounts.end as stakeFROm staking_solana.stake_actions), times as (--weeksSELECTq.timeFROM query_2254709 qWHERE q.time >= timestamp '{{Start Date}}'), rolling_diff as (with stake_diff as (SELECT*, COALESCE(sol_balance - lag(sol_balance,1) over (partition by address order by day asc nulls first), sol_balance) as sol_balance_diff --coalesce so that first deposit is counted into diffFROM solana_utils.daily_balancesWHERE token_mint_address is nulland address IN (SELECT distinct stake FROM stake_accounts))SELECTweek, total_sol_balance_diff, sum(total_sol_balance_diff) over (order by week asc nulls first) as total_sol_balanceFROM (SELECTdate_trunc('week', day) as week, sum(sol_balance_diff) as total_sol_balance_diffFROM stake_diffgroup by 1)), rolling_liquidity as (SELECTt.time-- , vote, total_sol_balance_diff, COALESCE(total_sol_balance, lag(total_sol_balance,1) ignore nulls OVER (order by t.time asc)) as total_sol_balanceFROM times tLEFT JOIN rolling_diff bal ON bal.week = t.time)SELECTtime, total_sol_balance_diff*(case when '{{Value}}' = 'USD' then p.price else 1 end) as total_sol_balance_diff, total_sol_balance*(case when '{{Value}}' = 'USD' then p.price else 1 end) as stake_solFROM rolling_liquidity rlLEFT JOIN prices.usd p ON p.blockchain = 'solana' and p.symbol = 'SOL' and p.minute = rl.time