SELECT

s.value::STRING AS "Address",

COUNT(DISTINCT tx_id) AS "Total_tx"

FROM

eclipse.core.fact_transactions,

LATERAL FLATTEN(input => signers) s

GROUP BY

1

),

user_ranks AS (

SELECT

"Address",

"Total_tx",

NTILE(100) OVER (ORDER BY "Total_tx" DESC) AS percentile

FROM

user_tx_counts

SELECT

COUNT(*) AS "Top 1% Users",

MIN("Total_tx") AS "Min tx to be among top 1%"

FROM

user_ranks

WHERE

percentile = 1;