WITH project_stats_all as (SELECT

COALESCE(project_name, to_address) as top_project,

COUNT(DISTINCT tx_hash) AS no_transactions,

COUNT(DISTINCT from_address) AS distinct_addresses,

SUM(tx_fee) as total_fees

FROM {{chain}}.core.fact_transactions

LEFT JOIN {{chain}}.core.dim_labels ON address = to_address

WHERE status = 'SUCCESS'

AND DATE(block_timestamp) > DATEADD('DAY', -90, CURRENT_TIMESTAMP())

GROUP BY 1

ORDER BY distinct_addresses DESC),

project_stats as (SELECT

date_trunc('week',block_timestamp) as date_time,

COALESCE(project_name, to_address) as project,

COALESCE(label_type, 'Not Labelled') as label,

COUNT(DISTINCT tx_hash) AS no_transactions,

COUNT(DISTINCT from_address) AS distinct_addresses,

SUM(tx_fee) as total_fees

FROM {{chain}}.core.fact_transactions

LEFT JOIN {{chain}}.core.dim_labels ON address = to_address

LEFT JOIN (SELECT DISTINCT(top_project) as t FROM project_stats_all) ON t = COALESCE(project_name, to_address)

WHERE status = 'SUCCESS'

AND DATE(block_timestamp) > DATEADD('DAY', -90, CURRENT_TIMESTAMP())

GROUP BY 1, 2, 3

ORDER BY distinct_addresses DESC)

project,

label,

no_transactions,

no_transactions / no_transactions_all as perc_transactions,

distinct_addresses,