SELECT

LABEL_TYPE AS PROJECT_TYPE,

-- PROJECT_NAME AS PROJECT,

SUM(FEE_NEAR) AS FEE_NEAR,

COUNT(*) AS TRANSACTIONS,

COUNT(DISTINCT TX_SIGNER) AS USERS,

COUNT(*) / COUNT(DISTINCT TX_SIGNER) AS TX_PER_USER

(

SELECT

T.BLOCK_TIMESTAMP,

TX_HASH,

TX_SIGNER,

TRANSACTION_FEE / POWER(10, 24) AS FEE_NEAR,

PROJECT_NAME,

LABEL_TYPE,

L.ADDRESS

FROM

NEAR.CORE.FACT_TRANSACTIONS T

LEFT JOIN NEAR.CORE.DIM_ADDRESS_LABELS L ON T.TX_RECEIVER = L.ADDRESS

WHERE

BLOCK_TIMESTAMP < CURRENT_DATE

)

WHERE PROJECT_TYPE IS NOT NULL

GROUP BY

1

ORDER BY TX_PER_USER DESC

limit 8