Create rows of intermediary dates

I'm looking to get the number of tickets that are unassigned at the end of each day over a specified period of days using historical data. I'm using Amazon Redshift.

The query currently has this structure:

ticket_id created_at assigned_at
1 2020-11-18 2020-11-20
2 2020-11-18 2020-11-18
3 2020-11-17 2020-11-20

My current strategy is to use the created_at date, create a new row with the created_date + 1 day until it is the same as the assigned_at date. From there I can easily manipulate the output with Tableau.

The output that I'm looking for is:

ticket_id created_at assigned_at
1 2020-11-18 2020-11-20
1 2020-11-19 2020-11-20
1 2020-11-20 2020-11-20
2 2020-11-18 2020-11-18
3 2020-11-17 2020-11-20
3 2020-11-18 2020-11-20
3 2020-11-19 2020-11-20
3 2020-11-20 2020-11-20

In the end I want to be able to get to this:

date tickets in queue
2020-11-17 1
2020-11-18 2
2020-11-19 2
2020-11-20 0

I'm also open to other suggestions regarding how to solve this problem.

Thanks in advance!

Continue reading...