SQL : Increment a new column whenever there is a multiple of 5 else dont

Question

I came across this question in a round of interview. A table has the following column.

ID
1
2
3
4
5
6
7
8
9
11
12
13
14
15
16
17
18
19
20
22
23
24
26

The question is to create a new column that starts with '1' and increments on the next ID whenever there is a multiple of 5. So the expected output is

ID	Result
1	1
2	1
3	1
4	1
5	1
6	2
7	2
8	2
9	2
11	2
12	2
13	2
14	2
15	2
16	3
17	3
18	3
19	3
20	3
22	4
23	4
24	4
26	4

Answer 1

You can combine two window functions: LAG() and SUM(). For example:

select id, 
  1 + sum(case when lid % 5 = 0 then 1 else 0 end) over(order by id) as v
from (
  select *, lag(id) over(order by id) as lid from t
) x
order by id

Result:

 id  v 
 --  - 
 1   1 
 2   1 
 3   1 
 4   1 
 5   1 
 6   2 
 7   2 
 8   2 
 9   2 
 11  2 
 12  2 
 13  2 
 14  2 
 15  2 
 16  3 
 17  3 
 18  3 
 19  3 
 20  3 
 22  4 
 23  4 
 24  4 
 26  4 

See running example at DB Fiddle.

Answer 2

For MySQL 5+ you may use, for example

SELECT id, (@result := COALESCE( @result + !(id % 5), 1 )) - !(id % 5) result
FROM t
CROSS JOIN (SELECT @result := NULL) init_variable
ORDER BY id

For MySQL 8+ use

SELECT id, 1 + SUM(!(id % 5)) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) resuls
FROM t

Answer 3

You can do this without a subquery:

select t.*,
       1 + sum( (id % 5) = 0 ) over (order by id) - (id % 5 = 0)
from t
order by id;

What is the logic here? Calculate the cumulative sum of "5"s up to this row. Then subtract out the value on this row, because the increment takes effect on the next row.

It is also tempting to write this using a window frame clause, but that ends up being a wee bit more complicated because the first value is NULL:

select t.*,
       1 + coalesce(sum( (id % 5) = 0 ) over (order by id rows between unbounded preceding and 1 preceding), 0)
from t
order by id;

Here is a db<>fiddle.