Query editor
WITH daily_net AS (SELECTCAST(date_trunc('day', block_time) AS date) AS day,SUM(CASEWHEN "to" = 0x1d4F25bC16b68C50B78E1040BC430a8097Fd6f45THEN amount_raw / 1e18WHEN "from" = 0x1d4F25bC16b68C50B78E1040BC430a8097Fd6f45THEN -(amount_raw / 1e18)ELSE 0END) AS net_amountFROM tokens.transfersWHERE contract_address = 0x5afe3855358e112b5647b952709e6165e1c1eeeeAND ("to" = 0x1d4F25bC16b68C50B78E1040BC430a8097Fd6f45OR "from" = 0x1d4F25bC16b68C50B78E1040BC430a8097Fd6f45)GROUP BY 1),calendar AS (SELECT dayFROM UNNEST(sequence((SELECT min(day) FROM daily_net),current_date,interval '1' day)) AS t(day)),cumulative_balance AS (SELECTc.day,SUM(COALESCE(d.net_amount, 0)) OVER (ORDER BY c.day) AS cumulative_balanceFROM calendar cLEFT JOIN daily_net dON c.day = d.day),daily_prices AS (SELECTCAST(date_trunc('day', block_time) AS date) AS day,SUM(amount_usd) / SUM(token_bought_amount_raw / 1e18) AS price_usdFROM dex.tradesWHERE token_bought_address = 0x5afe3855358e112b5647b952709e6165e1c1eeeeAND amount_usd IS NOT NULLAND token_bought_amount_raw IS NOT NULLAND token_bought_amount_raw > 0GROUP BY 1)SELECTcb.day,cb.cumulative_balance,dp.price_usdFROM cumulative_balance cbLEFT JOIN daily_prices dpON cb.day = dp.day