select

BLOCK_TIMESTAMP,

TX_HASH,

EVENT_INDEX,

DECODED_LOG:fee/1e4 as Pool_fee,

DECODED_LOG:pool as Pool_address,

DECODED_LOG:token0 as token0_address,

DECODED_LOG:token1 as token1_address

from ethereum.core.ez_decoded_event_logs

where contract_address = '0x1f98431c8ad98523631ae4a59f267346ea31f984'

and EVENT_NAME = 'PoolCreated'

and TX_STATUS = 'SUCCESS')

select

'Ethereum' as Network,

date_trunc('week', BLOCK_TIMESTAMP) as date,

count(distinct Pool_address) as Weekly_Created_Pools,

sum(Weekly_Created_Pools) over (order by date) as Cumulative_Created_Pools

from ethereum

group by 2

order by 2)

union all

(with base as (

select

BLOCK_TIMESTAMP,

TX_HASH,

EVENT_INDEX,

DECODED_LOG:fee/1e4 as Pool_fee,

DECODED_LOG:pool as Pool_address,

DECODED_LOG:token0 as token0_address,

DECODED_LOG:token1 as token1_address

from base.core.ez_decoded_event_logs

where contract_address = '0x4c36388be6f416a29c8d8eee81c771ce6be14b18'