Query editor
WITH gho_minters AS (SELECT onBehalfOf AS user,MIN(DATE_TRUNC('day', evt_block_time)) AS first_mintFROM aave_v3_ethereum.Pool_evt_BorrowWHERE reserve = 0x40d16fc0246ad3160ccc09b8d0d3a2cd28ae6c2fGROUP BY onBehalfOf), gho_new_minters_daily AS (SELECT first_mint, COUNT(DISTINCT user) AS new_usersFROM gho_mintersGROUP BY first_mint), gho_mints AS (SELECTDATE_TRUNC('day', evt_block_time) AS dt,COUNT(DISTINCT onBehalfOf) AS minters,COUNT(*) AS mints,sum(cast(amount as double)/pow(10,18)) as valueFROM aave_v3_ethereum.Pool_evt_BorrowWHERE reserve = 0x40d16fc0246ad3160ccc09b8d0d3a2cd28ae6c2fGROUP BY DATE_TRUNC('day', evt_block_time)), dt AS (SELECTt.dtFROM UNNEST(SEQUENCE(TRY_CAST('2023-07-15' AS DATE), CURRENT_DATE, INTERVAL '1' DAY) /* WARNING: Check out the docs for example of time series generation: https://dune.com/docs/query/syntax-differences/ */) AS t(dt))SELECTdt.dt,gho_mints.minters,gho_mints.mints,COALESCE(gho_new_minters_daily.new_users, 0) AS new_gho_minters,COALESCE(gho_mints.value, 0) AS GHO_minted,SUM(COALESCE(gho_new_minters_daily.new_users, 0)) OVER (ORDER BY dt.dt) AS cumulative_gho_minters,SUM(COALESCE(gho_mints.mints, 0)) OVER (ORDER BY dt.dt) AS cumulative_mints,SUM(COALESCE(gho_mints.value, 0)) OVER (ORDER BY dt.dt) AS c_GHO_minted,SUM(COALESCE(gho_mints.value, 0)/1000000) OVER (ORDER BY dt.dt) AS c_GHO_minted_mFROM dtLEFT JOIN gho_mintsON gho_mints.dt = dt.dtLEFT JOIN gho_new_minters_dailyON dt.dt = gho_new_minters_daily.first_mintORDER BY dt desc