I have a table like this:

I want to add one more column with previous date (not equal current date).

Desired table should looks like this:

Thanks for your help!

CodePudding user response：

Using a join along with the LAG function, we can try:

WITH cte AS (
    SELECT *, LAG(current_date) OVER
                  (PARTITION BY user_id ORDER BY current_date) previous_date
    FROM (SELECT DISTINCT current_date, user_id FROM yourTable) t
)

SELECT t1.current_date, t1.user_id, t2.previous_date
FROM yourTable t1
INNER JOIN cte t2
    ON t2.user_id = t1.user_id AND t2.current_date = t1.current_date
ORDER BY t1.user_id, t1.current_date;

Here is a demo in SQL Server, though the same query should run in Presto with minimal modifications needed.