query1 AS (

SELECT

DATE_TRUNC('day', ft.block_timestamp) AS dt,

COUNT(

DISTINCT CASE

WHEN dl.label_type = 'dex' THEN ft.from_address

END

) AS dex_count

FROM

optimism.core.fact_transactions ft

JOIN optimism.core.dim_labels dl ON ft.to_address = dl.address

WHERE

ft.block_timestamp >= '2019-01-01'

AND dl.label_type IN ('dex')

GROUP BY

dt

),

query2 AS (

SELECT

DATE AS dt,

SUM(VOLUME) AS optimism_vol

FROM

external.defillama.fact_dex_volume

WHERE

CHAIN = 'optimism'

AND DATE >= '2019-01-01'

GROUP BY

dt

),

sma AS (

SELECT

query1.dt,

query1.dex_count,

query2.optimism_vol,

query2.optimism_vol / query1.dex_count AS vol_per_dex_address,