select

price as current_price

from crosschain.price.ez_prices_hourly

where blockchain = 'stellar'

and symbol = 'XLM'

and is_native = true

and hour >= dateadd(year, -1, current_date())

order by hour desc

limit 1

),

fee_data as (

fee_account,

sum(fee_charged) as total_fees,

count(*) as tx_count,

avg(fee_charged) as avg_fee

from stellar.core.fact_transactions

where block_timestamp >= dateadd(year, -1, current_date())

group by 1

select

fee_data.fee_account,

to_char(fee_data.total_fees, 'FM999,999,999,999') as total_fees_stroops,

to_char(fee_data.total_fees / pow(10,7), 'FM999,999,999,999.0000000') as total_fees_xlm,

to_char(fee_data.tx_count, 'FM999,999,999,999') as tx_count,

to_char(fee_data.avg_fee, 'FM999,999,999,999') as avg_fee_stroops,

to_char(fee_data.avg_fee / pow(10,7), 'FM999,999,999,999.0000000') as avg_fee_xlm,

concat('$', to_char(stellar_price_data.current_price, 'FM999,999,999,999.0000000')) as xlm_price_usd,

concat('$', to_char((fee_data.total_fees / pow(10,7)) * stellar_price_data.current_price, 'FM999,999,999,999.00')) as total_fees_usd,

concat('$', to_char((fee_data.avg_fee / pow(10,7)) * stellar_price_data.current_price, 'FM999,999,999,999.0000000')) as avg_fee_usd

from fee_data

cross join stellar_price_data

order by fee_data.total_fees desc

limit 10;