Query editor
-- h/t https://dune.com/hildobby-- Original query: https://dune.com/queries/1933086/3188561-- Modified deposit dates section-- Added 7d / 30d changesWITH total_deposited AS (SELECT SUM(amount_staked) AS eth_depositedFROM staking_ethereum.deposits),eth_deposited_7_day AS (SELECT depositor_entity,SUM(amount_staked) AS eth_deposited_7_dayFROM staking_ethereum.depositsWHERE block_time >= date_add('day', -7, current_date)GROUP BY depositor_entity),eth_deposited_30_day AS (SELECT depositor_entity,SUM(amount_staked) AS eth_deposited_30_dayFROM staking_ethereum.depositsWHERE block_time >= date_add('day', -30, current_date)GROUP BY depositor_entity),dataset AS (SELECT ROW_NUMBER() OVER (ORDER BY SUM(amount_staked) DESC) AS ranking, CASE WHEN depositor_entity IS NULL THEN 'Others' ELSE depositor_entity END AS entity, SUM(amount_staked) AS eth_deposited, SUM(amount_staked)/32 AS validators, SUM(amount_staked)/(SELECT eth_deposited FROM total_deposited) AS marketshare, CAST(year(MIN(block_time)) AS varchar) || '-'|| lpad(CAST(month(MIN(block_time)) AS varchar), 2, '00') || '-'|| lpad(CAST(day(MIN(block_time)) AS varchar), 2, '00')AS first_deposit, CAST(year(MAX(block_time)) AS varchar) || '-'|| lpad(CAST(month(MAX(block_time)) AS varchar), 2, '00') || '-'|| lpad(CAST(day(MAX(block_time)) AS varchar), 2, '00')AS last_depositFROM staking_ethereum.depositsWHERE depositor_entity IS NOT NULLGROUP BY 2UNION ALLSELECT NULL AS ranking, 'Unidentified' AS entity, SUM(amount_staked) AS eth_deposited, SUM(amount_staked)/32 AS validators, SUM(amount_staked)/(SELECT eth_deposited FROM total_deposited) AS marketshare, CAST(year(MIN(block_time)) AS varchar) || '-'|| lpad(CAST(month(MIN(block_time)) AS varchar), 2, '00') || '-'|| lpad(CAST(day(MIN(block_time)) AS varchar), 2, '00')AS first_deposit, CAST(year(MAX(block_time)) AS varchar) || '-'|| lpad(CAST(month(MAX(block_time)) AS varchar), 2, '00') || '-'|| lpad(CAST(day(MAX(block_time)) AS varchar), 2, '00')AS last_depositFROM staking_ethereum.depositsWHERE depositor_entity IS NULL