Query editor
-- part of a query repo-- query name: LBTC-- query link: https://dune.com/queries/4365960-- One scan of the supply MV (4844928), densified per chain through a map, instead of inlining-- 4844926: Trino inlines each CTE reference, so the old spine + distinct-chain + join + guard-- references re-read the MV and a minute-grain prices.usd scan several times per run.-- Same rows as the 4844926 route: every chain present in the MV (any token), dates from the MV's-- first to last event date (any token), LBTC flows cumulative from 2024-06-01.WITH flows AS (SELECT"chain",CAST(DATE_TRUNC('day', evt_block_time) AS DATE) AS "date",SUM(CASE WHEN token = 'LBTC' THEN lbtc_amount ELSE 0 END) AS net_lbtc_flowFROM dune.lombard_protocol.result_lombard_deposits_and_withdrawals_v_2GROUP BY 1, 2),per_chain AS (SELECT "chain", MAP_AGG("date", net_lbtc_flow) AS flow_by_date, MIN("date") AS first_d, MAX("date") AS last_dFROM flowsGROUP BY 1),bounded AS (SELECT"chain",flow_by_date,GREATEST(MIN(first_d) OVER (), DATE '2024-06-01') AS spine_start,MAX(last_d) OVER () AS spine_endFROM per_chain),lombard_cumulative_net_flows AS (SELECTs."date",b."chain",SUM(COALESCE(ELEMENT_AT(b.flow_by_date, s."date"), 0))OVER (PARTITION BY b."chain" ORDER BY s."date" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_net_lbtc_flowFROM bounded bCROSS JOIN UNNEST(SEQUENCE(b.spine_start, b.spine_end, INTERVAL '1' DAY)) AS s("date")),-- prices.usd partitions on (blockchain, contract_address); a symbol filter alone reads every-- ethereum token. Only the midnight minute is joined, so only midnight minutes are kept.btc_prices AS (SELECT DATE_TRUNC('minute', minute) AS minute, priceFROM prices.usdWHERE blockchain = 'ethereum' AND symbol = 'WBTC'AND contract_address = 0x2260FAC5E5542a773Aa44fBCfeDf7C193bc2C599 -- WBTCAND minute >= TIMESTAMP '2024-08-21 00:00:00'AND DATE_TRUNC('minute', minute) = DATE_TRUNC('day', minute)),chain_values AS (SELECTl."date",l.chain,l.cumulative_net_lbtc_flow,l.cumulative_net_lbtc_flow * p.price as tvl,p.price as btc_priceFROM lombard_cumulative_net_flows lLEFT JOIN btc_prices pON l."date" = p.minute