a.contract_address,

COALESCE(c.project_name, 'No Label') AS contract_label, -- Get the label for each contract

COUNT(DISTINCT a.block_timestamp::date) AS "Active Days",

COUNT(DISTINCT a.origin_from_address) AS "Users",

COUNT(DISTINCT a.tx_hash) AS "Transactions",

COUNT(DISTINCT a.tx_hash) / COUNT(DISTINCT a.origin_from_address) AS "Avg Transaction Per User", -- Calculate avg transactions per user

SUM(b.tx_fee) AS "Fee (Klay)",

AVG(b.tx_fee) AS "Avg Fee (Klay)",

MEDIAN(b.tx_fee) AS "Median Fee (Klay)",

MAX(b.tx_fee) AS "Max Fee (Klay)"

FROM

kaia.core.fact_event_logs a

JOIN

kaia.core.fact_transactions b ON a.tx_hash = b.tx_hash

LEFT OUTER JOIN

kaia.core.dim_labels c ON a.contract_address = c.address

WHERE

a.tx_succeeded = 'TRUE'

AND a.block_timestamp::date >= '2024-01-01'

GROUP BY

a.contract_address, c.project_name -- Group by contract address and label

ORDER BY

COUNT(DISTINCT a.tx_hash) DESC -- Order by the number of transactions in descending order

LIMIT 20;zzz