Query editor
-- =====================================================================-- QUERY: cirBTC - Overview KPIs-- PURPOSE: Top-of-dashboard KPI strip. TVL = cumulative mints minus-- cumulative burns (no bridge contracts identified/labeled yet,-- so no bridge-outflow adjustment is applied today; once a-- bridge address is added to the label waterfall in the base-- query, it nets out automatically here with no SQL change,-- since this query reads is_mint/is_burn directly).-- PARAMETER: {{time_window}} one of 24h / 7d / 30d, controls the window-- used for the flow-based metrics (everything except TVL-- itself, which is always all-time-to-date by definition).-- NOTE: simplified to a single flat CTE chain (no correlated subqueries)-- after Dune's planner rejected the original nested-nested version-- as too many execution stages for this query's complexity.-- =====================================================================with base as (select * from query_7783594),window_flagged as (-- Flag whether each transfer falls inside the selected window, rather-- than filtering with a separate CTE — keeps this to one pass over-- the (tiny) base table instead of two.select*,evt_block_time >= now() - (case '{{time_window}}'when '24h' then interval '1' daywhen '7d' then interval '7' daywhen '30d' then interval '30' dayend) as in_windowfrom base),addr_touches as (select evt_tx_hash, in_window, "from" as addr from window_flaggedunion allselect evt_tx_hash, in_window, "to" as addr from window_flagged)selectsum(case when is_mint then amount else 0 end) - sum(case when is_burn then amount else 0 end) as tvl_now,sum(case when in_window and is_mint then amount else 0 end) as gross_inflow_mints,sum(case when in_window and is_burn then amount else 0 end) as gross_outflow_burns,sum(case when in_window and is_mint then amount else 0 end)- sum(case when in_window and is_burn then amount else 0 end) as net_change_in_window,(select count(distinct evt_tx_hash) from window_flagged where in_window) as num_transactions,(select count(distinct addr) from addr_touches where in_window) as active_addressesfrom window_flagged