with term as ( SELECT -- *, tx_hash, -- substr(data,3,64), -- origin_from_address, tokenflow_eth.hextoint(substr(data,3,64)) as term, tokenflow_eth.hextoint(right(data,32)) as rank from ethereum.core.fact_event_logs where origin_to_address = '0x06450dee7fd2fb8e39061434babcfc05599a6fb8' and origin_function_signature = '0x9ff054df' and topics[0] = '0xe9149e1b5059238baed02fa659dbf4bd932fbcf760a431330df4d934bc942f37' -- and tx_hash = '0xfa417f39f90dbb4ad5816216644f42f12c43561d16ac72e5fd7c99a45b9cc84d' -- limit 3 ) , periods as ( SELECT *, case when term = 1 then '1. 1 day' when term > 1 and term <= 30 then '2. 2-30 days' when term > 30 and term <= 60 then '3. 31-60 days' when term > 60 and term <= 90 then '4. 61-90 days' when term > 90 and term <= 120 then '5. 91-120 days' when term > 120 and term <= 150 then '6. 121-150 days' when term > 150 and term <= 180 then '7. 151-180 days' when term > 180 and term <= 210 then '8. 181-210 days' when term > 210 and term <= 240 then '9. 211-240 days' when term > 240 and term <= 270 then '10. 241-2700 days' when term > 270 and term <= 300 then '11. 271-300 days' when term > 300 and term <= 330 then '12. 301-330 days' when term > 330 and term <= 360 then '13. 331-360 days' when term > 360 and term <= 390 then '14. 361-390 days' when term > 360 and term <= 407 then '15. 391-407 days' when term = 408 then '16. 408 days' end as periods -- percentile_cont(0.2) within group (order by term), -- percentile_cont(0.4) within group (order by term), -- percentile_cont(0.6) within group (order by term), -- percentile_cont(0.8) within group (order by term), -- min(term), -- max(term) from term ) , count as ( SELECT periods, count(tx_hash) as no_of_txs, sum(count(tx_hash)) over () as cum_txs from periods group by 1 ) SELECT *, no_of_txs / cum_txs * 100 as txs_pct from count order by periods