SQL: Create an extra column with last 3 days date as a value

I have a table(users) with these sample data

user    location     name
111      usa          aaa
222      canada       bbb
333      usa          ccc
444      mexico       ddd
555      japan        eee

From this table, i want to get the final output like below

date               user    location     name
2020-11-03         111      usa          aaa
2020-11-03         222      canada       bbb
2020-11-03         333      usa          ccc
2020-11-03         444      mexico       ddd
2020-11-03         555      japan        eee
2020-11-02         111      usa          aaa
2020-11-02         222      canada       bbb
2020-11-02         333      usa          ccc
2020-11-02         444      mexico       ddd
2020-11-02         555      japan        eee
2020-11-01         111      usa          aaa
2020-11-01         222      canada       bbb
2020-11-01         333      usa          ccc
2020-11-01         444      mexico       ddd
2020-11-01         555      japan        eee

if you see above output, for each day(last 3 days), i am seeing user,location,name data.

Can anyone suggest a solution for this

Answer

You seem to want a cross join:

select v.date, t.*
from t cross join
     (values ('2020-11-03'), ('2020-11-02'), ('2020-11-01')) v(date);

You can easily adapt this for the last three days:

select current_date - v.n * interval '1 day', t.*
from t cross join
     (values (1), (2), (3)) v(n);

Advertisement

Answer