WITH top_20_dapps AS (

SELECT

case when ADDRESS_NAME is NULL then to_address else ADDRESS_NAME end as project_name,

COUNT(DISTINCT ft.FROM_ADDRESS) AS total_active_users

FROM

arbitrum.core.fact_transactions ft

INNER JOIN

arbitrum.core.dim_labels dl

ON ft.TO_ADDRESS = dl.ADDRESS

WHERE block_timestamp > current_date - 365

--AND label_type in ('dex', 'dapp', 'defi')

GROUP BY 1

ORDER BY

total_active_users DESC

LIMIT 100

),

daily_active_users AS (

SELECT

case when ADDRESS_NAME is NULL then to_address else ADDRESS_NAME end as project_name,

to_address,

COUNT(DISTINCT ft.FROM_ADDRESS) AS daily_active_users,

count(*) as interactions,

sum(tx_fee) as gas_fee_arb

FROM

arbitrum.core.fact_transactions ft

INNER JOIN

arbitrum.core.dim_labels dl

ON ft.TO_ADDRESS = dl.ADDRESS

WHERE

ADDRESS_NAME IN (SELECT PROJECT_NAME FROM top_20_dapps)

OR to_address IN (SELECT PROJECT_NAME FROM top_20_dapps)

AND block_timestamp > current_date - 365

GROUP BY 1,2