with wallet_profile as ( select from_address as signer , count(1) as txs , count_if(status = 'SUCCESS') as exe_txs , txs - exe_txs as rev_txs , round(rev_txs / txs, 3) AS wallet_fail_rate from {{chain}}.core.fact_transactions where 1 = 1 and block_timestamp::date = '{{target_date}}' group by 1 ) , cte1 AS ( SELECT CASE WHEN txs <= 5 THEN 1 WHEN txs > 5 AND txs < 10 THEN 2 WHEN txs >= 10 AND txs < 100 THEN 3 WHEN txs >= 100 AND txs < 500 THEN 4 WHEN txs >= 500 AND txs < 1000 THEN 5 WHEN txs >= 1000 AND txs < 2500 THEN 6 WHEN txs >= 2500 AND txs < 5000 THEN 7 WHEN txs >= 5000 AND txs < 10000 THEN 8 WHEN txs >= 10000 AND txs < 25000 THEN 9 WHEN txs >= 25000 THEN 10 END AS txs_bucket , CASE WHEN txs <= 5 THEN '5 or less tx' WHEN txs > 5 AND txs < 10 THEN '<10' WHEN txs >= 10 AND txs < 100 THEN '<100' WHEN txs >= 100 AND txs < 500 THEN '<500' WHEN txs >= 500 AND txs < 1000 THEN '<1000' WHEN txs >= 1000 AND txs < 2500 THEN '<2500' WHEN txs >= 2500 AND txs < 5000 THEN '<5000' WHEN txs >= 5000 AND txs < 10000 THEN '<10000' WHEN txs >= 10000 AND txs < 25000 THEN '<25000' WHEN txs >= 25000 THEN '25000+' END AS bucket_label , count(DISTINCT signer) AS unique_signers , sum(txs) AS bucket_txs , sum(exe_txs) AS executed_txs , sum(rev_txs) AS reverted_txs , round(100 * reverted_txs / bucket_txs, 2) AS bucket_fail_percent , round(avg(wallet_fail_rate), 3) AS avg_wallet_fail_rate , max(txs) , min(txs) , round(100 * ratio_to_report(unique_signers) OVER (), 3) AS bucket_signers_ratio , round(100 * ratio_to_report(reverted_txs) OVER (), 2) AS bucket_fail_tx_ratio , round(100 * ratio_to_report(bucket_txs) OVER (), 2) AS bucket_daily_tx_ratio FROM wallet_profile GROUP BY all ORDER BY txs_bucket ) SELECT bucket_label AS "Wallet Tx Count" , unique_signers as "Distinct Signers" , bucket_txs as "Transactions" , bucket_fail_percent as "Category Tx Fail Percent" , bucket_signers_ratio AS "Percent of Unique Signers" , bucket_daily_tx_ratio AS "Percent of Total Tx" , bucket_fail_tx_ratio AS "Percent of Total Failed Tx" , txs_bucket FROM cte1 order by txs_bucket