Query editor
WITH btc_supply AS (SELECT SUM(50/POWER(2, ROUND(height/210000))) AS supplyFROM bitcoin.blocks), btc_amounts AS (SELECT SUM(f.amount) AS tvl, (SUM(f.amount) FILTER (WHERE block_time > NOW() - interval '7' day)) AS past_week_flows, (SUM(f.amount) FILTER (WHERE block_time >= date('2024-01-10'))) AS flows_usd_since_approval, (SUM(f.amount) FILTER (WHERE block_time > NOW() - interval '7' day)) AS past_week_flows_usd, 100*SUM(f.amount)/(SELECT supply FROM btc_supply) AS percentage_of_btc, SUM(f.amount) FILTER (WHERE block_time > NOW() - interval '6' month) AS past_sixmonths_flows, SUM(f.amount) FILTER (WHERE block_time > NOW() - interval '3' month) AS past_threemonths_flows, SUM(f.amount) FILTER (WHERE block_time > NOW() - interval '1' month) AS past_month_flows, SUM(f.amount) FILTER (WHERE block_time > NOW() - interval '14' day) AS past_twoweeks_flows, (SELECT supply FROM btc_supply) AS btc_supplyFROM (SELECT i.block_time, a.issuer, ticker AS etf_ticker, 'deposit' AS flow_type, CASE WHEN inverse_values THEN -i.value ELSE i.value END AS amountFROM bitcoin.outputs iINNER JOIN dune.hildobby.dataset_bitcoin_etf_addresses -- Known ETF address lista ON a.address=i.addressAND a.track_inflowINNER JOIN dune.hildobby.dataset_bitcoin_etf_metadata eat ON a.issuer=eat.issuerWHERE i.value > 0AND i.block_time > date('2019-07-24')--AND i.block_time <= (SELECT CAST(bitcoin AS timestamp) AS time_limit FROM dune.hildobby.dataset_etf_update_thresholds)UNION ALLSELECT i.block_time, a.issuer, ticker AS etf_ticker, 'withdrawal' AS flow_type, CASE WHEN inverse_values THEN i.value ELSE -i.value END AS amountFROM bitcoin.inputs iINNER JOIN dune.hildobby.dataset_bitcoin_etf_addresses -- Known ETF address lista ON a.address=i.addressAND a.track_outflowINNER JOIN dune.hildobby.dataset_bitcoin_etf_metadata eat ON a.issuer=eat.issuerWHERE i.value > 0AND i.block_time > date('2019-07-24')AND i.block_time <= (SELECT CAST(bitcoin AS timestamp) AS time_limit FROM dune.hildobby.dataset_etf_update_thresholds)UNION ALLSELECT CAST("date" AS timestamp) AS block_time, 'Fidelity' AS issuer, 'FBTC' AS etf_ticker, 'withdrawal' AS flow_type, -amount AS amountFROM dune.hildobby.dataset_bitcoin_etf_fidelity_outflowsUNION ALLSELECT t.block_time, et.issuer