Query editor
WITH time_series AS (SELECT timeFROM (unnest(sequence(date('2018-10-16'), date(NOW()), interval '1' hour)) AS s(time))WHERE time > date_trunc('hour', NOW() - interval '5' year)), flows AS (SELECT date_trunc('hour', t.block_time) AS time, a.issuer, SUM(CASE WHEN a.inverse_values = true THEN -t.amount ELSE t.amount END) AS flowFROM tokens_ethereum.transfers tINNER JOIN dune.hildobby.dataset_ethereum_etf_addresses a ON a.address=t.toAND a.track_inflow = trueWHERE t.token_standard = 'native'AND t.block_time >= date_trunc('hour', NOW() - interval '5' year)AND t.block_time >= date('2018-10-16')AND t.block_time <= (SELECT CAST(ethereum AS timestamp) AS time_limit FROM dune.hildobby.dataset_etf_update_thresholds)GROUP BY 1, 2UNION ALLSELECT date_trunc('hour', t.block_time) AS time, a.issuer, -1*SUM(CASE WHEN a.inverse_values = true THEN -t.amount ELSE t.amount END) AS flowFROM tokens_ethereum.transfers tINNER JOIN dune.hildobby.dataset_ethereum_etf_addresses a ON a.address=t."from"AND a.track_outflow = trueWHERE t.token_standard = 'native'AND t.block_time > date_trunc('hour', NOW() - interval '5' year)AND t.block_time >= date('2018-10-16')AND t.block_time <= (SELECT CAST(ethereum AS timestamp) AS time_limit FROM dune.hildobby.dataset_etf_update_thresholds)GROUP BY 1, 2UNION ALLSELECT CAST("date" AS timestamp) AS time, 'Fidelity' AS issuer, -amount AS flowFROM dune.hildobby.dataset_ethereum_etf_fidelity_outflowsWHERE CAST("date" AS timestamp) > date_trunc('hour', NOW() - interval '5' year)AND CAST("date" AS timestamp) <= (SELECT CAST(ethereum AS timestamp) AS time_limit FROM dune.hildobby.dataset_etf_update_thresholds)UNION ALLSELECT t.block_time, et.issuer, 0 AS flowFROM (unnest(sequence(date('2024-07-21'), date(NOW()), interval '1' hour)) AS s(block_time)) tCROSS JOIN (SELECT issuer FROM dune.hildobby.dataset_ethereum_etf_metadata) etWHERE t.block_time > date_trunc('hour', NOW() - interval '5' year)AND t.block_time <= (SELECT CAST(ethereum AS timestamp) AS time_limit FROM dune.hildobby.dataset_etf_update_thresholds)), expanded_flows AS (SELECT ts.time