SELECT

tx_signer,

COUNT(DISTINCT tx_hash) as count_TXs,

CASE WHEN count_TXs = 1 then '1 Transactions'

WHEN count_TXs > 1 AND count_TXs <= 10 then '2-10 Transactions'

WHEN count_TXs > 10 AND count_TXs <= 100 then '11-100 Transactions'

WHEN count_TXs > 100 AND count_TXs <= 1000 then '101-1000 Transactions'

WHEN count_TXs > 1001 AND count_TXs <= 10000 then '1001-10000 Transactions'

WHEN count_TXs > 10001 AND count_TXs <= 100000 then '10001-100000 Transactions'

ELSE '100000+ Transactions' END AS type

FROM

near.core.fact_transactions

WHERE

TX_STATUS = 'Success'

AND

tx_receiver IN ('aurora')

GROUP BY 1

SELECT

trunc(BLOCK_TIMESTAMP,'week') AS date,

type,

COUNT(DISTINCT t1.tx_hash) AS total_TXs,

COUNT(DISTINCT t1.tx_signer) AS total_users

FROM

near.core.fact_transactions t1 JOIN Distribution_users t2 ON t1.tx_signer = t2.tx_signer

GROUP BY 1,2