Query editor
WITH unaggregated AS (SELECT i.block_time, a.issuer, eat.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 a ON a.address=i.address AND a.track_inflowINNER JOIN dune.hildobby.dataset_bitcoin_etf_metadata eat ON a.issuer=eat.issuerWHERE i.value > 0AND i.block_time > date_trunc('hour', NOW() - interval '5' year)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, eat.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 a ON a.address=i.address AND a.track_outflowINNER JOIN dune.hildobby.dataset_bitcoin_etf_metadata eat ON a.issuer=eat.issuerWHERE i.value > 0AND i.block_time > date_trunc('hour', NOW() - interval '5' year)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_outflowsWHERE CAST("date" AS timestamp) > date_trunc('hour', NOW() - interval '5' year)AND CAST("date" AS timestamp) <= (SELECT CAST(bitcoin AS timestamp) AS time_limit FROM dune.hildobby.dataset_etf_update_thresholds)UNION ALLSELECT t.block_time, et.issuer, et.ticker AS etf_ticker, NULL AS flow_type, 0 AS amountFROM (unnest(sequence(date('2018-09-27'), date(NOW()), interval '1' hour)) AS s(block_time)) tINNER JOIN dune.hildobby.dataset_bitcoin_etf_metadata et ON 1=1WHERE t.block_time > date_trunc('hour', NOW() - interval '5' year)AND t.block_time <= (SELECT CAST(bitcoin AS timestamp) AS time_limit FROM dune.hildobby.dataset_etf_update_thresholds)), hourly_aggregates AS (SELECT date_trunc('hour', f.block_time) AS time, f.issuer, f.etf_ticker, SUM(f.amount) AS amount, SUM(f.amount*pu.price) AS amount_usdFROM unaggregated f