t1 AS (

SELECT

-- block_timestamp::date AS utc_date

-- , (CASE WHEN utc_date >= CURRENT_DATE - interval '30 days' THEN 'Past 30 days' ELSE 'Past year' END) AS time_period

distinct account_keys[0]:pubkey::string AS unique_addresses

, sum(fee)/1e9 as total_fees

--, count(*) / 24 / 60 / 60 AS tps

-- , avg(CASE WHEN succeeded = True THEN 100.0000 ELSE 0.0000 END) AS success_rate

FROM solana.core.fact_transactions

WHERE block_timestamp::date >= CURRENT_DATE - interval '365 days'

and succeeded = 'FALSE'

and fee>0

GROUP BY 1

SELECT t1.unique_addresses, t2.label, t2.address_name, t1.total_fees

FROM t1

left join solana.core.dim_labels as t2

on t1.unique_addresses=t2.address

ORDER BY 4 DESC