-- forked from Users Categorized by Number of Transactions @ https://flipsidecrypto.xyz/edit/queries/e7c69ed1-c218-4ce8-ac6c-167cc1aec84c WITH MarginFi_users AS ( SELECT DISTINCT SIGNERS[0] AS user FROM solana.core.fact_events WHERE program_id = 'dRiftyHA39MWEi3m9aunc5MzRF1JYuBsbn6VPcn33UH' AND block_timestamp::DATE >= '2023-02-07' ), activities AS ( SELECT SIGNERS[0] AS user, COUNT(DISTINCT tx_id) AS n_txs, CASE WHEN n_txs = 1 THEN '1 transaction' WHEN n_txs <= 90 * 1/5 THEN '<=' || FLOOR(90 * 1/5) || ' txns' WHEN n_txs <= 90 * 1/2 THEN '<=' || FLOOR(90 * 1/2) || ' txns' WHEN n_txs <= 90 * 1 THEN '<=' || FLOOR(90 * 1) || ' txns' WHEN n_txs <= 90 * 2 THEN '<=' || FLOOR(90 * 2) || ' txns' WHEN n_txs <= 90 * 5 THEN '<=' || FLOOR(90 * 5) || ' txns' WHEN n_txs <= 90 * 50 THEN '<=' || FLOOR(90 * 50) || ' txns' ELSE '>' || FLOOR(90 * 50) || ' txns' END AS type, CASE WHEN n_txs = 1 THEN 'one txn' WHEN n_txs <= 90 * 1/5 THEN 'every 5 days 1 txn' WHEN n_txs <= 90 * 1/2 THEN 'every 2 days 1 txn' WHEN n_txs <= 90 * 1 THEN 'one txn per day' WHEN n_txs <= 90 * 2 THEN '>2 txns per day' WHEN n_txs <= 90 * 5 THEN '>5 txns per day' WHEN n_txs <= 90 * 50 THEN '>50 txns per day' ELSE '<50 txns per day' END AS tx_interval, CASE WHEN tx_interval = 'one txn' THEN 'g1' WHEN tx_interval = 'every 5 days 1 txn' THEN 'g2' WHEN tx_interval = 'every 2 days 1 txn' THEN 'g3' WHEN tx_interval = 'one txn per day' THEN 'g4' WHEN tx_interval = '>2 txns per day' THEN 'g5' WHEN tx_interval = '>5 txns per day' THEN 'g6' WHEN tx_interval = '>50 txns per day' THEN 'g7' ELSE 'g8' END AS groups FROM solana.core.fact_events WHERE block_timestamp::DATE >= current_date -90 AND user in (SELECT user from MarginFi_users) GROUP BY 1 ) SELECT groups, type, tx_interval, COUNT(user) AS count_user FROM activities GROUP BY 1, 2, 3 ORDER BY 1

Drift Protocol Data Dashboard