Split single row to multiple rows based on date range - Amazon Redshift

I need to query data from a Redshift table that represents a room stay. A stay for multiple days will be one single row, but I need those stays to be split into multiple rows to represent a single night per row, with the roomrevenue column to be split by the number of nights (there is a column for this too).

This is an example of the table I'm talking about and the relevant fields:

This is what I actually want as the resulting query:

You can use recursion to split the revenue for each day.

First start with dividing roomrevenueusd by lengthofstay, then the recursion condition continues to append the individual dates till it reached the departure date.

Syntax is in Sql Server, you might need to change some of the syntax accordingly for Redshift, however DATEADD should be similar in Redshift.

Sample Query

WITH recursion_room_stay AS (
SELECT stayid, arrivaldate AS staydate,
CAST(roomrevenueusd / lengthofstay AS DECIMAL(10, 2)) AS each_roomrevenueusd,
arrivaldate, departuredate
  
FROM test
  
UNION ALL
  
SELECT  stayid,
DATEADD(day, 1, staydate),
each_roomrevenueusd,arrivaldate,
departuredate
FROM recursion_room_stay
WHERE DATEADD(day, 1, staydate) < departuredate
)
SELECT  stayid,staydate, each_roomrevenueusd
FROM recursion_room_stay
ORDER BY stayid desc, staydate
;

for the input

Generates

Example Fiddle