SELECT

Address,

INITCAP(Address_name) AS Address_name,

INITCAP(label_type) AS label,

INITCAP(Project_name) AS Project_name

FROM

near.core.dim_address_labels

WHERE

label_type NOT IN ('cex', 'token')

AND Project_name IS NOT NULL

),

nearprice AS (

SELECT

TRUNC(hour, 'day') AS day,

AVG(price) AS price

FROM

near.price.ez_prices_hourly

WHERE

symbol = 'NEAR'

GROUP BY

day

),

top_projects AS (

-- Calculate total fee for each project and get the top 10 projects

SELECT

npj.Project_name,

SUM((nf.transaction_fee / 1e24) * np.price) AS total_fee_usd

FROM

near.core.fact_transactions nf

JOIN

nearprice np

ON DATE_TRUNC('day', nf.block_timestamp) = np.day

JOIN

nearproject npj

ON nf.tx_receiver = npj.Address