Query editor
WITHtimes as (--daysSELECTq.time, ip.account_whirlpool as whirlpool_idFROM query_2254698 q, whirlpool_solana.whirlpool_call_initializePool ipWHERE time >= (SELECT min(call_block_time) FROM whirlpool_solana.whirlpool_call_initializePool)AND ip.account_whirlpool IN (SELECT project_program_id FROM orca_whirlpool.trades) --only gets liquidity from traded pools), whirlpool_liquidity_a as (SELECTaccount_whirlpool as whirlpool_id, account_tokenVaultA as tokenVaultA, db.token_mint_address as tokenA, db.token_balance as tokenA_balance, db.day, row_number() over (partition by account_tokenVaultA order by db.day desc) as latestFROM whirlpool_solana.whirlpool_call_initializePool ipLEFT JOIN solana_utils.daily_balances dbON db.address = ip.account_tokenVaultAAND db.token_mint_address is not null), whirlpool_liquidity_b as (SELECTaccount_whirlpool as whirlpool_id, account_tokenVaultB as tokenVaultB, db.token_mint_address as tokenB, db.token_balance as tokenB_balance, db.day, row_number() over (partition by account_tokenVaultB order by db.day desc) as latestFROM whirlpool_solana.whirlpool_call_initializePool ipLEFT JOIN solana_utils.daily_balances dbON db.address = ip.account_tokenVaultBAND db.token_mint_address is not null), rolling_liquidity as (--forward and backfill value across all timeSELECTtime, whirlpool_id, coalesce(tokenA, lag(tokenA, 1) IGNORE NULLS OVER (partition by whirlpool_id ORDER BY time asc), lead(tokenA, 1) IGNORE NULLS OVER (partition by whirlpool_id ORDER BY time asc)) as tokenA, coalesce(tokenB, lag(tokenB, 1) IGNORE NULLS OVER (partition by whirlpool_id ORDER BY time asc), lead(tokenB, 1) IGNORE NULLS OVER (partition by whirlpool_id ORDER BY time asc)) as tokenB, coalesce(tokenA_balance, lag(tokenA_balance, 1) IGNORE NULLS OVER (partition by whirlpool_id ORDER BY time asc)) as tokenA_balance, coalesce(tokenB_balance, lag(tokenB_balance, 1) IGNORE NULLS OVER (partition by whirlpool_id ORDER BY time asc)) as tokenB_balanceFROM (SELECTt.time, t.whirlpool_id, tokenA, tokenB, tokenA_balance, tokenB_balanceFROM times tLEFT JOIN whirlpool_liquidity_a a ON a.whirlpool_id = t.whirlpool_id AND a.day = t.timeLEFT JOIN whirlpool_liquidity_b b ON b.whirlpool_id = t.whirlpool_id AND b.day = t.time) base