WITH

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,

COUNT(

DISTINCT CASE

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

END

) AS defi_count,

COUNT(

DISTINCT CASE

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

END

) AS dapp_count,

COUNT(

DISTINCT CASE

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

END

) AS nft_count,

COUNT(

DISTINCT CASE

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

END

) AS layer2_count

FROM

ethereum.core.fact_transactions ft

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

WHERE

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