Query editor
WITH time_series AS (SELECT timeFROM (unnest(sequence(date('2018-11-18'), date(NOW()), interval '1' day)) AS s(time))WHERE time > date_trunc('day', NOW() - interval '5' year)),flows AS (SELECT date_trunc('day', 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('day', NOW() - interval '5' year)AND t.block_time >= date('2017-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('day', 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('day', NOW() - interval '5' year)AND t.block_time >= date('2017-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('day', NOW() - interval '5' year)AND CAST("date" AS timestamp) <= (SELECT CAST(ethereum AS timestamp) AS time_limit FROM dune.hildobby.dataset_etf_update_thresholds)),expanded_flows AS (SELECT ts.time, i.issuer, COALESCE(SUM(f.flow), 0) AS flowFROM time_series tsCROSS JOIN (SELECT issuer FROM dune.hildobby.dataset_ethereum_etf_metadata) iLEFT JOIN flows f ON ts.time = f.time AND i.issuer = f.issuerGROUP BY 1, 2), data AS (SELECT CAST(f.time AS timestamp) AS time, f.issuer, SUM(f.flow) AS tvl, pu.price*SUM(f.flow) AS tvl_usd