Query editor
-- h/t https://dune.com/hildobby-- Original Query: https://dune.com/queries/1941407/3202651WITH time_series AS (SELECT timeFROM (unnest(sequence(date('2020-11-02'), date(NOW()), interval '7' day)) AS s(time))), data AS (SELECT date_trunc('week', block_time) AS time, entity_category, SUM(amount_staked)-SUM(amount_full_withdrawn) AS stakedFROM query_2393816GROUP BY 1, 2), nulls AS (SELECT distinct ts.time, eth2.entity_categoryFROM time_series ts, query_2393816 eth2UNION ALLSELECT distinct ts.time, 'Unidentified' AS entity_categoryFROM time_series ts), filled_data AS (SELECT n.time, n.entity_category, COALESCE(SUM(d.staked), 0) AS stakedFROM nulls nLEFT JOIN data d ON d.time=n.timeAND (n.entity_category=d.entity_categoryOR n.entity_category='Unidentified' AND d.entity_category IS NULL)GROUP BY 1, 2), notable_or_not AS (SELECT entity_category, CASE WHEN staked > 320 THEN true ELSE false END AS is_notableFROM filled_dataWHERE time = (SELECT MAX(time) FROM filled_data)), negigible_removed AS (SELECT fd.time, CASE WHEN non.is_notable = false THEN 'Unidentified'ELSE fd.entity_categoryEND AS entity_category, fd.stakedFROM filled_data fdLEFT JOIN notable_or_not non ON non.entity_category=fd.entity_category), ranking AS (SELECT ROW_NUMBER() OVER (ORDER BY SUM(staked) DESC) AS ranking