Create ClickHouse Materialized View for State Analytics (Mainnet)

[CPerezz]

For the state-exploration panel I'm working on, I actually need state-growth metrics for all-time-ethereum.
The query is massive, thus, I propose the following:

Problem

State analytics queries timeout (60+ seconds) scanning mainnet.canonical_execution_storage_diffs (10.6B rows). Feature is unusable.

Solution

Create 1 materialized view with monthly aggregations. Yearly view can be computed by summing months (only ~120 months total = trivial) so we can skip the view for it.

What We Need

Execute this single SQL statement:

CREATE MATERIALIZED VIEW IF NOT EXISTS mainnet.mv_monthly_state_growth_by_address
ENGINE = SummingMergeTree()
PARTITION BY toYear(month)
ORDER BY (month, address)
AS
SELECT
    toStartOfMonth(toDateTime(1438269973 + block_number * 12)) AS month,
    address,
    countIf(from_value = '' OR from_value = '0x0000000000000000000000000000000000000000000000000000000000000000') AS slots_added,
    countIf(to_value = '0x0000000000000000000000000000000000000000000000000000000000000000') AS slots_cleared,
    countIf(from_value = '' OR from_value = '0x0000000000000000000000000000000000000000000000000000000000000000')
        - countIf(to_value = '0x0000000000000000000000000000000000000000000000000000000000000000') AS net_slots,
    (countIf(from_value = '' OR from_value = '0x0000000000000000000000000000000000000000000000000000000000000000')
        - countIf(to_value = '0x0000000000000000000000000000000000000000000000000000000000000000')) * 191 AS net_bytes
FROM mainnet.canonical_execution_storage_diffs
GROUP BY month, address;

Resource Impact

Storage: ~50 MB (0.0005% of source table)
Creation time: 2-12 hours (one-time, background)
Maintenance: Auto-updates, zero effort

I'm not sure if this is the repo where I should create such request. So let me know if that makes sense!

cc: @samcm

[weiihann]

The latest transformation tables here will provide what you need:
ethpandaops/xatu-cbt#94

[CPerezz]

Thanks a lot @weiihann <3