SELECT

date_trunc('{{Interval}}',block_timestamp) as date,

count(distinct origin_from_address) as "Optimism Users",

count(distinct tx_hash) as "Optimism Tx"

FROM optimism.core.fact_event_logs

WHERE block_timestamp > CURRENT_DATE - {{day}}

and block_timestamp::date <= CURRENT_DATE - 1

GROUP BY 1

),

arbitrum AS (

SELECT

date_trunc('{{Interval}}',block_timestamp) as date,

count(distinct origin_from_address) as "Arbitrum Users",

count(distinct tx_hash) as "Arbitrum Tx"

FROM arbitrum.core.fact_event_logs

WHERE block_timestamp > CURRENT_DATE - {{day}}

and block_timestamp::date <= CURRENT_DATE - 1

GROUP BY 1

),

polygon AS (

SELECT

date_trunc('{{Interval}}',block_timestamp) as date,

count(distinct origin_from_address) as "Polygon Users",

count(distinct tx_hash) as "Polygon Tx"

FROM polygon.core.fact_event_logs

WHERE block_timestamp > CURRENT_DATE - {{day}}

and block_timestamp::date <= CURRENT_DATE - 1

GROUP BY 1

select

a.date,

"Optimism Tx"/ "Optimism Users" as "Optimism Tx/Address",

"Arbitrum Tx"/ "Arbitrum Users" as "Arbitrum Tx/Address",

"Polygon Tx"/ "Polygon Users" as "Polygon Tx/Address",

avg("Optimism Tx") as "Avg Optimism Tx",