I have a T1 table with user_id and the date they created their account either in 2019 or 2018. I want to assign a random date to each user. For users in 2018 the random date can be anything in 2019, but for people registered in 2019 there random date must lie between after one day of account creation and 31st Dec 2019.
Despite using order by and setting seed (redshift allows only random to give a number between 0 to 1), I am getting a new random date for the same user_id, when I re-run the below code. This is on redshift and I'm using alation(SQL) for query.
set seed to 0.25;
select *,
case when created_date<'2019-01-01' then date(DATEADD(day,cast(random()*DATEDIFF(day,'2019-01-01','2019-12-31') as int),'2019-01-01'))
when created_date<'2020-01-01' and created_date>='2019-01-01' then date(DATEADD(day,cast(random()*DATEDIFF(day,created_date,'2019-12-31') as int),created_date))
end as random_date
from scratchdb.tmp_table1
order by 1,2
