Generating table of intervals (1,10),(11,20),...,(101,200),...,(1001,2000),...

It's often useful to present statistical results by interval representing frequencies but not necessarily evenly spaced,
example with (task, person) associations,
how many persons with only 1 task, with 2 to 10, with 11 to 20, … with 101 to 200, …


with ranges(lvl, strt, step) as (
    select 2 as lvl, 0 as strt, 10*power(10,trunc((1-2)/9,0)) as step from dual
    union all
    select lvl+1, strt + step, 10*power(10,trunc((lvl-2)/9,0)) from ranges
    where lvl <= 20
)
select 1 as f, 1 as t, 'Only once' as l, 0 as s from dual
union
select
    case when f=0 then 2 else f+1 end,
    t,

   case when f=0 then 2 else f+1 end || ' - ' || t as l, lvl as s
from (
     select lvl-1 as lvl, strt as f, (lead(strt,1,9999999999) over(order by lvl)) as t
    from ranges
);
F
T
L
S
1
1
Only once
0
2
10
2 - 10
1
11
20
11 - 20
2





You can play with the depth of the recursion by changing the "where lvl <= 20" condition and change the intervals by changing the "10*power" (e.g. using 100*power", you will get 2-100,101-200, … intervals).
Using this table in a JOIN query with your data source statistics will give you the results presenting the frequencies.

F
T
L
S
1
1
Only once
0
2
100
2 - 10
1
101
200
101 - 200
2