hour::date AS timespan,

avg (price) AS price

FROM ethereum.price.ez_prices_hourly

WHERE symbol = 'WETH'

GROUP BY 1

),

blob_base AS (

DISTINCT tx_hash,

block_number,

block_timestamp,

CASE

WHEN from_address = '0x0d3250c3d5facb74ac15834096397a3ef790ec99' THEN 'zkSyncEra: Batcher'

WHEN from_address = '0x2c169dfe5fbba12957bdd0ba47d9cedbfe260ca7' THEN 'StarkNet: Operator'

WHEN from_address = '0x6887246668a3b87f54deb3b94ba47a6f63f32985' THEN 'Optimism: Batcher'

WHEN from_address = '0x5050f69a9786f081509234f1a7f4684b5e5b76c9' THEN 'Base: Batch Sender'

WHEN from_address = '0xc1b634853cb333d3ad8663715b08f41a3aec47cc' THEN 'Arbitrum: Batch Submitter'

WHEN from_address = '0xa9268341831efa4937537bc3e9eb36dbece83c7e' THEN 'Linea'

WHEN from_address = '0x415c8893d514f9bc5211d36eeda4183226b84aa7' THEN 'Blast' -- issues

WHEN from_address = '0xcf2898225ed05be911d3709d9417e86e0b4cfc8f' THEN 'Scroll: Batch Comitter'

WHEN from_address = '0x625726c858dbf78c0125436c943bf4b4be9d9033' THEN 'Zora'

WHEN from_address = '0x889e21d7ba3d6dd62e75d4980a4ad1349c61599d' THEN 'Aevo' --issues

WHEN from_address = '0x41b8cd6791de4d8f9e0eaf7861ac506822adce12' THEN 'Kroma'

WHEN from_address = '0x14e4e97bdc195d399ad8e7fc14451c279fe04c8e' THEN 'Lyra' -- issues

WHEN from_address = '0x99199a22125034c808ff20f377d91187e8050f2e' THEN 'Mode'

WHEN from_address = '0x6017f75108f251a488b045a7ce2a7c15b179d1f2' THEN 'Fraxtal' -- issues

WHEN from_address = '0x99526b0e49a95833e734eb556a6abaffab0ee167' THEN 'PGN' -- issues

WHEN from_address = '0xc70ae19b5feaa5c19f576e621d2bad9771864fe2' THEN 'Paradex' -- issues

WHEN from_address = '0xc94c243f8fb37223f3eb2f7961f7072602a51b8b' THEN 'Metal'

WHEN from_address = '0xe1b64045351b0b6e9821f19b39f81bc4711d2230' THEN 'Boba Network'

WHEN from_address = '0x08f9f14ff43e112b18c96f0986f28cb1878f1d11' THEN 'Camp Network'

WHEN from_address = '0x5ead389b57d533a94a0eacd570dc1cc59c25f2d4' THEN 'Public Goods Network'