How to create column for every single integer within a range in SQLite?

Question

Here's some sample data from my table:

day_number  daily_users_count
1           1
3           1
6           1
7           1
9           2
10          2

I need all day_number values, from 1 to max(day_number), and I want daily_users_count to be zero if it isn't mentioned in this table.

It should look something like this:

day_number  daily_users_count
1           1
2           0
3           1
4           0
5           0
6           1
7           1
8           0
9           2
10          2

I think a left join with a table which has a number column with all integers from 1 to max(day_number) would work, if I put a default value for daily_users_count as 0.

What I don't get is how to create such a table where all integers within a certain range are present. Any alternate solutions or any ways to do this would be much appreciated.

score 1 · Accepted Answer · answered Aug 29 '20 at 09:30

You can do it with a recursive CTE which will return all the day_numbers including the missing ones and then a LEFT join to the table:

with cte as (
  select min(day_number) day_number from tablename
  union all
  select day_number + 1 from cte
  where day_number < (select max(day_number) from tablename)
)  
select c.day_number, 
       coalesce(t.daily_users_count, 0) daily_users_count
from cte c left join tablename t
on t.day_number = c.day_number

See the demo.
Results:

| day_number | daily_users_count |
| ---------- | ----------------- |
| 1          | 1                 |
| 2          | 0                 |
| 3          | 1                 |
| 4          | 0                 |
| 5          | 0                 |
| 6          | 1                 |
| 7          | 1                 |
| 8          | 0                 |
| 9          | 2                 |
| 10         | 2                 |

How to create column for every single integer within a range in SQLite?

1 Answers1