Split data into X days' Cohort (SQL Server)
Your desired outcome is that you can change one value (the cohort width) to have all rows re-allocated to the new groupings.

I'd use a separate table to hold the upper and lower bounds for each cohort. These values can be generated a run-time according to the width parameter. There are two special cases - the day-zero cohort which only ever contains a single value and the last cohort which has an unlimited upper bound. There will have to be a second parameter to say how many "normal" cohorts there must be. To generate this I will use a "numbers" table.

create table #Cohorts
(
    Label       varchar(20) not NULL,
    LowerBound  int         not NULL,
    UpperBound  int         not NULL
);


declare @Width int = 10;
declare @Depth int = 5;

insert #Cohorts
(
    Label,
    LowerBound,
    UpperBound
)
select
    'day_' + CONVERT(varchar(20), ((n + 1) * @Width)),
    1 + (n * @Width),
    ((n + 1) * @Width)
from Numbers
where n &lt;= @Depth

union all

select 'day_0', 0, 0        -- the special case for day_0

union all

select
    'day_' + CONVERT(varchar(20), ((@Depth + 1) * @Width)) + 'plus',  -- a special label for the last cohort
    1 + ((@Depth + 1) * @Width),    -- the upper limit of the dynamic cohorts
    2147483647                      -- the maximum value of an int; pseudo-infinity


For the values given in the question this returns

Label       LowerBound  UpperBound
----------  ----------  ----------
day_0       0           0
day_10      1           10
day_20      11          20
day_30      21          30
day_40      31          40
day_50      41          50
day_60      51          60
day_60plus  61          2147483647


In your original question you have used (open, close] bounds. For simplicity I have used [closed, closed]. It doesn't matter since the bounds are integers and the cohorts are contiguous. It just means your &gt;  and &lt;= becomes my &gt;= and &lt;=. With tinkering your convention could be used equally well.

We assign each data row to a cohort by joining to this temporary table. The counting is done using GROUP BY.

select
    c.Label, COUNT(*)
from your_data_table as d
inner join #Cohorts as c
     on c.LowerBound &lt;= d.by_day
     and d.by_day &lt;= c.UpperBound
group by c.Label;


This will give one row per cohort. To get a single row with a column per cohort requires a PIVOT. There is a T-SQL statement to do this. There are other methods, too, of which your sum(case..) is one.