with totalblockgas as (

SELECT DATE_TRUNC('hour', block_timestamp) as hour, SUM(GAS_USED) as "Gas Used" FROM blast.core.fact_blocks

WHERE block_timestamp >= '2024-06-12 16:00:00.000'

GROUP BY 1

),

blocks as (

SELECT DATE_TRUNC('hour', a.block_timestamp) as hour, TO_ADDRESS as "Contract", SUM(a.GAS_USED) as "Gas Used By Contract", (SUM(a.GAS_USED)*100)/b."Gas Used" as "%ofBlocksGas", b."Gas Used" FROM blast.core.fact_transactions a

INNER JOIN totalblockgas b ON DATE_TRUNC('hour', a.block_timestamp) = b.hour

WHERE a.block_timestamp >= '2024-06-12 16:00:00.000'

GROUP BY 1,2, b."Gas Used"

SELECT hour, SUM("Gas Used By Contract") as "Gas Used By Contracts", SUM("%ofBlocksGas") as "%ofHoursGas" FROM blocks

WHERE "Contract" IN (

LOWER('0xf77dd21c5ce38ac08786BE35Ef1d1DeC1a6a15F3'),

LOWER('0xCf09205D04647B0dbF99fe702113358DF764cED3'),

LOWER('0x8c3EDe5dB70719aB9191655994880b088DD03917'),

LOWER('0x4d90cc30B9E75679d3Cf89B011053946DfE8763b'),

LOWER('0x96648d17c273A932197aCF2232653Bed7D69EC6f'),

LOWER('0xA596f7D6587DE656d0Ef099d2F28fe699060BF97'),

LOWER('0xc6f17f7935B12a38fe4a80f4f4Db7DBaf324224c'),

LOWER('0x0000000000E14E87e5c80A8A90817308fFF715d3'),

LOWER('0x9C9D8C8ee91f21004A40c4664D49CC1b9DFa69f9'),

LOWER('0x28EF3eaE1AbB6D6e22e9bFc7a0944f707E4726b3'),

LOWER('0x0000000000acc01064aa5280da3f1c41a35827bc'),

LOWER('0x693B37a9859Ce9465Fb2aAdeB03811a26A0c37C0'),

LOWER('0xf83493045Bc4a21D3Be8095bc93AbbaD7d216916'),

LOWER('0x6a53C1c24f433F055cd3F056Ab5Fd9CDE8f29856'),

LOWER('0xA56A95F41e64Bc76CDE7423aB2A2ee1763bD8Bcc'),

LOWER('0x7a9BD150C2dD86e7889ef3407f4654b3Ef507C36'),

LOWER('0x352dC3340942e9D80a88b20370766f77daC65624'),

LOWER('0x00000000007fe8d7666bb0da2a5d13f72b8dabab'),

LOWER('0xa975C618421e56C41A46Dc890Da0DE1150f04836'),

LOWER('0x119Ad581D07a0D8a5746798aA1367e8c54162961'),

LOWER('0x84A9B331a21B04Cc7F1d9719Cb5c1acA122cc1D8'),