-- reference : https://app.flipsidecrypto.com/dashboard/sushiswap-dex-loyal-users-9dWX4n with failed as ( select tx_hash from near.core.fact_receipts where Parse_json(status_value):"Failure" is not null ), average_price as ( select timestamp::date as "date", token_contract,avg(price_usd) as avg_price from near.core.fact_prices group by "date",token_contract ), swap as ( select fc.tx_hash, fc.block_timestamp, fc.method_name, -- fc.args, t.tx_receiver as platform, t.tx_signer as swapper, PARSE_JSON(args):actions[0]:"amount_in" as amount_in, PARSE_JSON(args):actions[0]:"pool_id" as pool_id, PARSE_JSON(args):actions[0]:"token_in" as token_in, PARSE_JSON(args):actions[0]:"token_out" as token_out from near.core.fact_actions_events_function_call fc inner join near.core.fact_transactions t on fc.tx_hash=t.tx_hash where t.tx_hash not in (select * from failed) and fc.method_name in ('swap','ft_transfer_call','proxy_swap') and platform='v2.ref-finance.near' ), swap_data as ( select s.tx_hash, s.block_timestamp, s.method_name, s.swapper, s.pool_id, case when token_in='wrap.near' then 'wnear' when token_in='dac17f958d2ee523a2206206994597c13d831ec7.factory.bridge.near' then 'USDT' when token_in='token.v2.ref-finance.near' then 'REF' when token_in='a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48.factory.bridge.near' then 'usdc' when token_in='6b175474e89094c44da98b954eedeac495271d0f.factory.bridge.near' then 'DAI' when token_in='f5cfbc74057c610c8ef151a439252680ac68c6dc.factory.bridge.near' then 'OCT' when token_in='meta-pool.near' then 'STNEAR' when token_in='token.paras.near' then 'PARAS' when token_in='token.skyward.near' then 'SKYWARD' when token_in='aurora' then 'aurora' else 'other' end as symbol_in, case when token_out='wrap.near' then 'wnear' when token_out='dac17f958d2ee523a2206206994597c13d831ec7.factory.bridge.near' then 'USDT' when token_out='token.v2.ref-finance.near' then 'REF' when token_out='a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48.factory.bridge.near' then 'usdc' when token_out='6b175474e89094c44da98b954eedeac495271d0f.factory.bridge.near' then 'DAI' when token_out='f5cfbc74057c610c8ef151a439252680ac68c6dc.factory.bridge.near' then 'OCT' when token_out='meta-pool.near' then 'STNEAR' when token_out='token.paras.near' then 'PARAS' when token_out='token.skyward.near' then 'SKYWARD' when token_out='aurora' then 'aurora' else token_out end as symbol_out, case when token_in='wrap.near' then '24' when token_in='dac17f958d2ee523a2206206994597c13d831ec7.factory.bridge.near' then '6' when token_in='token.v2.ref-finance.near' then '24' when token_in='a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48.factory.bridge.near' then '6' when token_in='6b175474e89094c44da98b954eedeac495271d0f.factory.bridge.near' then '18' when token_in='f5cfbc74057c610c8ef151a439252680ac68c6dc.factory.bridge.near' then '18' when token_in='meta-pool.near' then '24' when token_in='token.paras.near' then '18' when token_in='token.skyward.near' then '18' when token_in='aurora' then '18' else '24' end as decimal, ap.avg_price , ap.avg_price*amount_in/power(10,decimal)as amount_usd from swap s inner join average_price ap on (ap."date"=s.block_timestamp::date and ap.token_contract=s.token_in)), combined as ( select swapper, count(distinct tx_hash) as n_trades, count(distinct block_timestamp::date) as n_days from swap_data where block_timestamp > current_date - interval '{{time_duration}} month' group by swapper order by n_trades desc, n_days desc ) select case when n_days > 30 and n_trades >3000 then 'A. Bots: >30 days and >3k swaps' when n_days > 25 and n_trades between 250 and 3000 then 'B. Constant high-trade users: >25 days and 250-3k swaps' when n_days > 25 and n_trades <250 then 'C. Constant low-trade user: >25 days and <250 swaps' when n_days between 10 and 25 and n_trades >250 then 'D. Active user: 10-25 days and >250 swaps' when n_days between 10 and 25 and n_trades <250 then 'E. Passive user: 10-25 days and >250 swaps' when n_days <10 and n_trades >250 then 'F. Sporadic high-trade user : <10 days and >250 swaps' when n_days between 2 and 10 and n_trades <250 then 'G. Sporadic low-trade user: <10 days and <250 swaps' when n_days <2 then 'H. Tester: <2 days and any swaps' else 'Others' end as type, count(distinct swapper) as users from combined group by type order by type

Ref.Finance, the Bank of Near ?