SELECT to_char(block_timestamp, 'YYYY-MM-DD') as time, sum(GAS_USED) as GAS_USED

FROM blast.core.fact_transactions txs

WHERE EXISTS (

select 1

from blast.core.fact_event_logs block

where contract_address in (

'0xc48f6213a1a4d3ead79db812c2b91817980532c3', -- game

'0x74d9c915c64ab7587a3abde1827f3a09eac97bac', -- the last samurai

'0xaf466677d96e6df2e31a86fda7e54a31d64647ed' -- flip coin

)

AND block.tx_hash = txs.tx_hash

)

GROUP BY to_char(block_timestamp, 'YYYY-MM-DD')

),

total_gas_used AS (

SELECT to_char(block_timestamp, 'YYYY-MM-DD') as time, sum(GAS_USED) as GAS_USED

FROM blast.core.fact_blocks

WHERE blockchain = 'blast'

AND network = 'mainnet'

GROUP BY to_char(block_timestamp, 'YYYY-MM-DD')

SELECT sss.time, sss.GAS_USED/nu.GAS_USED * 100 AS percent

from sss_gas_used AS sss

LEFT JOIN total_gas_used nu

ON nu.time = sss.time

order by time desc