Query editor
WITH flows AS (SELECT CASE WHEN t.block_time < date('2024-07-23') THEN 'Pre Launch'WHEN t.block_time BETWEEN date('2024-07-23') AND NOW() - interval '30' day THEN 'After Launch'WHEN t.block_time BETWEEN NOW() - interval '30' day AND NOW() - interval '14' day THEN 'Past Month'WHEN t.block_time BETWEEN NOW() - interval '14' day AND NOW() - interval '7' day THEN 'Past 2 Weeks'WHEN t.block_time > NOW() - interval '7' day THEN 'Past Week'END AS timing, SUM(CASE WHEN a.inverse_values = true THEN -t.amount ELSE t.amount END) AS flow, SUM(pu.price*(CASE WHEN a.inverse_values = true THEN -t.amount ELSE t.amount END)) AS flow_usdFROM tokens_ethereum.transfers tINNER JOIN dune.hildobby.dataset_ethereum_etf_addresses a ON a.address=t.toAND a.track_inflow = trueLEFT JOIN prices.usd pu ON pu.blockchain IS NULLAND pu.symbol = 'ETH'AND pu.minute=date_trunc('minute', t.block_time)WHERE t.token_standard = 'native'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 1UNION ALLSELECT CASE WHEN t.block_time < date('2024-07-23') THEN 'Pre Launch'WHEN t.block_time BETWEEN date('2024-07-23') AND NOW() - interval '30' day THEN 'After Launch'WHEN t.block_time BETWEEN NOW() - interval '30' day AND NOW() - interval '14' day THEN 'Past Month'WHEN t.block_time BETWEEN NOW() - interval '14' day AND NOW() - interval '7' day THEN 'Past 2 Weeks'WHEN t.block_time > NOW() - interval '7' day THEN 'Past Week'END AS timing, -1*SUM(CASE WHEN a.inverse_values = true THEN -t.amount ELSE t.amount END) AS flow, SUM(pu.price*(CASE WHEN a.inverse_values = true THEN -t.amount ELSE t.amount END)) AS flow_usdFROM tokens_ethereum.transfers tINNER JOIN dune.hildobby.dataset_ethereum_etf_addresses a ON a.address=t."from"AND a.track_outflow = trueLEFT JOIN prices.usd pu ON pu.blockchain IS NULLAND pu.symbol = 'ETH'AND pu.minute=date_trunc('minute', t.block_time)WHERE t.token_standard = 'native'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), total_aum AS (SELECT CAST(SUM(flow) AS double) AS tvlFROM flows), past_week AS (SELECT CAST(SUM(flow) AS double) AS past_week_flowsFROM flowsWHERE timing = 'Past Week'), past_two_weeks AS (SELECT CAST(SUM(flow) AS double) AS past_two_weeks_flowsFROM flowsWHERE timing IN ('Past Week', 'Past 2 Weeks')), past_month AS (