Query editor
-- PreStocks: Weekly LP Revenue (USDC + WSOL)-- Revenue = realized withdrawals + unrealized quote assets still in pool vaults-- Weeks start Thursday 9AM EST (14:00 UTC)---- Memory-optimized: splits inflows/outflows into separate scans that each-- aggregate to weekly immediately (tiny result sets), then combines.-- No JOINs on the transfers table — only IN filters + early aggregation.WITHpools (pool_address) AS (VALUES('HCSLogB34igiJ2Y84kSSbUvwEeCQwH2D2hmMEgAhEc63'),('gPchZYUR4prBpYr4BQDZdB4fwYxWSh8h2rY83CQxN61'),('Hh2e8C27zuMPyGa2TqfaCLWbqVtQ5ByfJZMqqDnEzMxm'),('v4D5b4knJ83WzErtDiugxChUZUfWxe2FtDLguarvgFc'),('283tAkQVKT89svN1jCZbHyMsrzRwwYBRuTPrT2y5Ldtj'),('GbtX5AEEcki3jXs9RPvb9abSYxnJvtJVoAZkk4W96dPu'),('9DuT2XuEWrRA1VSRVmNdiwPoSr357PEL2Zh6m3ZWBXEV'),('6qqo7sRPpKyntaiW4rdTsh11xnEnFgT4vUtnZAsxdG1u'),('5mx74pZdvGym9eD1C5iCRqu26Ru6c9aUbURAuE5GXkic'),('4wa8FTHDBLNf1DCAWhNbqJ5ZRGkV8TQX1TaF2VMpNttM'),('nShkPgyWW4LcMRvgfCsxvs2wTKPrmYG29DuxZuWbu5J'),('A5q13xW5nZLLVFm3UScFg6RHJTQKJzSDnW1aRQp47ffB'),('HgGVFL55BxhkGW8qyp8dPPG1ua6UFgz5VEF6Y5SMxtgt'),('ATyzkzqySd6vpyvChdE78zXqGoaN1SbrY4oo4hU6WHfw'),('94SNe8W4faZzReJktCc5QpayyqrEQuQ1XT7P9WAjWQ2R'),('FoL5dFhV7XUootAbrqDdA72oXL8HExspLNRt1tuJNv53'),('B8t81GxsSeKLfFi8WHxNNKTU2Reiay4pmxUDpc8gK4vn'),('86sBRbopV2Qz6ZUbuMqZeYhrS3iCpy2xc3hCHXF8wJuG'),('B8p43nx7K8g87vR7EL5sRt25xNxkAPpV5mmReL3GZr3r'),('HhABC2WjgUq6xYUSA92diUuQ8A9rxoXaWbN38KQkhZSt'),('HQ7kdGdHKBTZVuwA4U69kXrjjCqzctkvpDC51nXjUJEn'),('EZyszDEx1LZDt7TsSFV8xdPi49sDKC3mdfv2MVMEQLtU'),('HUobUVo1pcMGPqbx346C1s4jhSFx4Jq7rX4XmEyoKYN4'),('6LoRVRgkWEpsm2xLGbFwUGpAW5r4bpYFBCWV4NVcYyFx'),('HReDCdjb5aaNNstUGMQw8CDE6DFjNLs6x6yqmqQuMZ6D'),('8MRxd2cRV2mC5mkczq7s9p8U494N9AdbZE2FSb7CdC3C'),('Gug9TrrLMoz8NpC4pE8ANQ6H7XzMMZYPuu1EJCn59ZwM'),('DZwJYn5ZgC3ZzJPjdzLnpufr3YHJ8PveNyXomzoh1Md4'),('CoqYNCGKpiGgLJJ6DntdbcvKmqTZBrxdvQDrxDCfb9GH'),('Y6wSJPjwcnfY8sHM2b3gTht3gGCGdf838gRkQJpfNYe'),('5BcgrYkQYxH46p55j6TRUaSu3Bg93uhucov2uYdtwWX3'),('7eBKQTLLv8QCjSMgCw1fQkBB5Xj2yTM2y9Vp8WzdCSij')),-- Scan 1: USDC/WSOL flowing INTO pool vaults (aggregate immediately)inflows AS (SELECTDATE_TRUNC('week', tr.block_time - INTERVAL '3' DAY - INTERVAL '14' HOUR)+ INTERVAL '3' DAY + INTERVAL '14' HOUR AS week,CASE WHEN tr.token_mint_address = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'THEN 'USDC' ELSE 'WSOL'END AS symbol,SUM(CAST(tr.amount AS DOUBLE) / CASEWHEN tr.token_mint_address = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'THEN 1e6 ELSE 1e9END) AS pool_delta,-SUM(CASE WHEN tr.from_owner IN ('AuDS1jWvD2StHgkFfFUYaxa4rKQCjAqGayNSC1feixrV','BVqFgP13mjy2p5koeTDCPRoHj2PLSMrABtCu2nSKUTXV'