with dau as (with tab1 as (with total_weeks as ( select count(distinct complete_week) as n_total_weeks from ( select date_trunc('week', BLOCK_TIMESTAMP::date) as complete_week, count(distinct date_trunc('day', BLOCK_TIMESTAMP::date)) as n_days from osmosis.core.fact_transactions group by 1 having n_days = 7 ) ), total_days as ( select count(distinct date_trunc('day', BLOCK_TIMESTAMP::date)) as n_total_days from osmosis.core.fact_transactions where block_timestamp < CURRENT_DATE ), completeweeks as ( select date_trunc('week', BLOCK_TIMESTAMP::date) as complete_weeks, count(distinct date_trunc('day', BLOCK_TIMESTAMP::date)) as n_days from osmosis.core.fact_transactions where block_timestamp < CURRENT_DATE group by 1 having n_days = 7 ), main as ( select TX_FROM, date_trunc('week', BLOCK_TIMESTAMP::date) as weeks, date_trunc('day', BLOCK_TIMESTAMP::date) as day_of_the_week, count(tx_id) as txs from osmosis.core.fact_transactions where block_timestamp < CURRENT_DATE group by 1 , 2 , 3 order by 3 ), activity as ( select TX_FROM, weeks, count(distinct DAY_OF_THE_WEEK) as days, sum(txs) as tx from main join completeweeks on complete_weeks = weeks group by 1 , 2 order by 2 ), results as ( select TX_FROM, count(distinct weeks) as active_weeks, sum(tx) as total_txs from activity where days >= 5 group by 1 order by 2 , 3 ) select TX_FROM, active_weeks, (active_weeks/n_total_weeks*100) as percent_of_all_complete_weeks, total_txs from results, total_weeks where TX_FROM is not null order by 2 desc , 3 desc, 4 desc), tab2 as ( with overview as ( select count(distinct BLOCK_TIMESTAMP::date) as n_days, count(distinct TX_ID) as n_txs, count(distinct TX_FROM) as n_users from osmosis.core.fact_transactions ), tab11 as ( select TX_FROM as users, count(distinct BLOCK_TIMESTAMP::date) as n_day from osmosis.core.fact_transactions group by 1 order by 2 DESC ) select users, n_day, n_days, (n_day/n_days*100) as percent_of_days from tab11 join overview where USERS is not null order by 4 desc ) select a.TX_FROM as "Users", a.active_weeks as "Active Weeks", b.n_day as "Active Days", a.percent_of_all_complete_weeks as "% of All Weeks", b.percent_of_days as "% of All Days", a.total_txs as "Total Txs" from tab1 a inner join tab2 b on tx_from=users where a.percent_of_all_complete_weeks >= 50 -- Activity in more than half full weeks order by 4 desc, 5 desc, 2 desc , 3 desc, 6 desc) select case when "Total Txs" >= '0' and "Total Txs" < '100' then '0-0.1k Transactions' when "Total Txs" >= '100' and "Total Txs" < '250' then '0.1k-0.25k Transactions' when "Total Txs" >= '250' and "Total Txs" < '500' then '0.25k-0.5k Transactions' when "Total Txs" >= '500' and "Total Txs" < '1000' then '0.5k-1k Transactions' when "Total Txs" >= '1000' and "Total Txs" < '2500' then '1k-2.5k Transactions' when "Total Txs" >= '2500' and "Total Txs" < '5000' then '2.5k-5k Transactions' when "Total Txs" >= '5000' and "Total Txs" < '10000' then '5k-10k Transactions' when "Total Txs" >= '10000' and "Total Txs" < '50000' then '10k-50k Transactions' when "Total Txs" >= '50000' and "Total Txs" < '250000' then '50k-250k% Transactions' when "Total Txs" >= '250000' then '> 250k Transactions' end as txs_status, count(distinct "Users") from dau group by 1 order by 1

Query	Creator	Created
AXL in Osmosis Chain osmosis ibc balances	hbd1994	Dec 2, 2022
Vote Changes State osmosis ibc governance	hbd1994	Nov 24, 2022
Transaction Status osmosis ibc	hbd1994	Oct 18, 2022
Top Senders of Tokens Transferred to DAUs osmosis ibc	hbd1994	Oct 20, 2022

hbd1994