Top Accounts by Transactions within the last 12 months

permary Top Accounts by Transactions within the last 12 months

Updated 6 days ago

with xlm_prices as (

select

date_trunc('month', hour) as month,

avg(price) as avg_monthly_price

from stellar.price.ez_prices_hourly

where blockchain = 'stellar'

and is_native = true

and hour >= date_trunc('month', current_date) - interval '12 months'

group by month

)

select

t.account,

to_char(count(*), 'fm999,999,999') as total_transactions,

to_char(sum(t.fee_charged) / 10000000.0, 'fm999,999,999,999.0000000') as total_fees_paid_xlm,

concat('$', to_char(sum((t.fee_charged / 10000000.0) * p.avg_monthly_price), 'fm999,999,999,999.00')) as total_fees_paid_usd

from stellar.core.fact_transactions t

left join xlm_prices p

on date_trunc('month', t.closed_at) = p.month

where t.closed_at >= date_trunc('month', current_date) - interval '12 months'

group by t.account

order by count(*) desc

limit 10;

Last run: 6 days ago

	ACCOUNT	TOTAL_TRANSACTIONS	TOTAL_FEES_PAID_XLM	TOTAL_FEES_PAID_USD
1	GDLYECYW23R4K7LLMCNK6ZG4BMA6ODESNTUHNK56E3DOW6723VJ2HX5J	5,266,879	1,052.1916290	$192.53
2	GBNHHWVHIP7GT3UNIP6O7H5C6WSMVYSJR62AJJRSGO3ECRNPOJI3MELV	4,227,845	6,395.1425890	$1,233.23
3	GAWMXRPIAMZ5GDBAOKKI3BZXZWIKRX3ALAH3ENPFCIGAE4IHCP5NGPG2	3,944,662	48.7788130	$9.30
4	GCYHACG4RDWBPBODEDFPSAV6XOUMLYE7LD66I3CWZ4Z4B4QJGHOCVWP3	3,613,399	37.0423870	$7.56
5	GDZRSRB4DOK3372HO2OKYVJKGTL5MYF5VUSO5CD5CJJNQVG35HMBQT6U	3,516,463	35.9902800	$7.47
6	GC7L4BVK43SBE3U445M2GXP2FY2AGYMEUO5V5B6GWWFHP2JOXBWOHOA3	3,341,374	311.4732510	$85.23
7	GCLM2V4D45RTGXTUYB6LU32Q3IUWZC5NN4KF5S6SCYXEZZLUOIKDKCJI	3,252,217	33.4124360	$6.54
8	GAFLVHUGQACYPJCYHNKPVNVTPJSQQ5ED3UYPFGJQTQUVNZ5FFWIIW463	3,209,532	254.1051890	$75.37
9	GAWLY7WEF5GGT5WCX2MMJEZG3CHLW73P7QJWOL7YWKV2LVH7NVL67ZVN	2,612,488	467.2145620	$81.85
10	GDZ23WE4AZA6WXGRSRMQXH5JNUNOMY63V5KYEQ7KTV2QMDDUC7O4LK5Q	2,543,693	173.3541250	$41.34

961B

60s

flipside