Query editor
WITH-- 1. Отток из Base (L2 -> L1)outflow_l2 AS (SELECTdate_trunc('day', block_time) AS day,SUM(CAST(value AS DOUBLE) / 1e18) AS eth_outFROM base.transactionsWHERE "to" = 0x4200000000000000000000000000000000000010 -- L2 Standard BridgeAND success = trueAND block_time > now() - interval '30' dayAND value > 0GROUP BY 1),-- 2. Приток в Base (L1 -> L2)inflow_l1 AS (SELECTdate_trunc('day', block_time) AS day,SUM(CAST(value AS DOUBLE) / 1e18) AS eth_inFROM ethereum.transactionsWHERE "to" = 0x49048044D57e1C92A77f79988d21Fa8fAF74E97e -- Base: Portal (L1)AND success = trueAND block_time > now() - interval '30' dayAND value > 0GROUP BY 1),-- 3. Цены ETHeth_prices AS (SELECTdate_trunc('day', minute) AS day,AVG(price) AS avg_priceFROM prices.usdWHERE blockchain = 'ethereum'AND symbol = 'WETH'AND minute > now() - interval '30' dayGROUP BY 1)SELECTCOALESCE(i.day, o.day) AS day,ROUND(COALESCE(i.eth_in, 0), 2) AS eth_inflow,ROUND(-COALESCE(o.eth_out, 0), 2) AS eth_outflow,ROUND(COALESCE(i.eth_in, 0) - COALESCE(o.eth_out, 0), 2) AS net_eth_flow,ROUND((COALESCE(i.eth_in, 0) - COALESCE(o.eth_out, 0)) * p.avg_price, 2) AS net_usd_flowFROM inflow_l1 iFULL OUTER JOIN outflow_l2 o ON i.day = o.dayLEFT JOIN eth_prices p ON COALESCE(i.day, o.day) = p.dayORDER BY 1 DESC