Query editor
WITH usde_inflow AS(SELECT DATE_TRUNC('day', evt_block_time) AS date,SUM(cast("value"/1e18 AS DOUBLE)) AS usde_inflowFROM erc20_ethereum.evt_TransferWHERE "contract_address" = 0x4c9edd5852cd905f086c759e8383e09bff1e68b3AND "to" = 0x02950460e2b9529d0e00284a5fa2d7bdf3fa4d72AND "evt_block_time" >= CAST('2023-11-20' AS TIMESTAMP)GROUP BY 1),usde_outflow AS(SELECT DATE_TRUNC('day', evt_block_time) AS date,-SUM(cast("value"/1e18 AS DOUBLE)) AS usde_outflowFROM erc20_ethereum.evt_TransferWHERE "contract_address" = 0x4c9edd5852cd905f086c759e8383e09bff1e68b3AND "from" = 0x02950460e2b9529d0e00284a5fa2d7bdf3fa4d72AND "evt_block_time" >= CAST('2023-11-20' AS TIMESTAMP)GROUP BY 1),usdc_inflow AS(SELECT DATE_TRUNC('day', evt_block_time) AS date,SUM(cast("value"/1e6 AS DOUBLE)) AS usdc_inflowFROM erc20_ethereum.evt_TransferWHERE "contract_address" = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48AND "to" = 0x02950460e2b9529d0e00284a5fa2d7bdf3fa4d72AND "evt_block_time" >= CAST('2023-11-20' AS TIMESTAMP)GROUP BY 1),usdc_outflow AS (SELECT DATE_TRUNC('day', evt_block_time) AS date,-SUM(cast("value" /1e6 AS DOUBLE)) AS usdc_outflowFROM erc20_ethereum.evt_TransferWHERE "contract_address" = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48AND "from" = 0x02950460e2b9529d0e00284a5fa2d7bdf3fa4d72AND "evt_block_time" >= CAST('2023-11-20' AS TIMESTAMP)GROUP BY 1),txs AS(SELECT DATE_TRUNC('day', evt_block_time) AS dateFROM erc20_ethereum.evt_TransferWHERE "contract_address" = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2AND "evt_block_time" >= CAST('2023-11-20' AS TIMESTAMP)GROUP BY 1)SELECTtxs.date,SUM("usde_inflow") OVER (ORDER BY txs.date) + SUM("usde_outflow") OVER (ORDER BY txs.date) AS usde_balance,SUM("usdc_inflow") OVER (ORDER BY txs.date) + SUM("usdc_outflow") OVER (ORDER BY txs.date) AS usdc_balance,(SUM("usde_inflow") OVER (ORDER BY txs.date) + SUM("usde_outflow") OVER (ORDER BY txs.date))+(SUM("usdc_inflow") OVER (ORDER BY txs.date) + SUM("usdc_outflow") OVER (ORDER BY txs.date)) AS tvl,(SUM("usde_inflow") OVER (ORDER BY txs.date) + SUM("usde_outflow") OVER (ORDER BY txs.date)) / CAST(((SUM("usde_inflow") OVER (ORDER BY txs.date) + SUM("usde_outflow") OVER (ORDER BY txs.date)) + (SUM("usdc_inflow") OVER (ORDER BY txs.date) + SUM("usdc_outflow") OVER (ORDER BY txs.date))) AS DOUBLE) AS usde_weight,(SUM("usdc_inflow") OVER (ORDER BY txs.date) + SUM("usdc_outflow") OVER (ORDER BY txs.date)) / CAST(((SUM("usde_inflow") OVER (ORDER BY txs.date) + SUM("usde_outflow") OVER (ORDER BY txs.date)) + (SUM("usdc_inflow") OVER (ORDER BY txs.date) + SUM("usdc_outflow") OVER (ORDER BY txs.date))) AS DOUBLE) AS usdc_weightFROM txsLEFT JOIN usde_inflow ON txs.date = usde_inflow.dateLEFT JOIN usde_outflow ON txs.date = usde_outflow.dateLEFT JOIN usdc_inflow ON txs.date = usdc_inflow.dateLEFT JOIN usdc_outflow ON txs.date = usdc_outflow.dateORDER BY txs.date DESC NULLS FIRST