Display the previous date for a user in an additional column-CodePudding

I have a list of users and a list of review dates corresponding to each user, the user can have multiple reviews relating to them. What I need to do is create an additional column that shows me the users previous review date, if they don't have a previous review I need it to be null. An example of the result I require is shown below with the column in bold being the column I want to add:

| User | Review Date | Previous Review Date
| ----- | -------------- | ------------------------

| 1122334 | 01/01/2022 | 06/06/2021
| 1122334 | 06/06/2021 | 06/01/2021
| 1122334 | 06/01/2021 | null
| 2244668 | 01/10/2021 | 01/04/2021
| 2244668 | 01/04/2021 | null
| 3344556 | 10/11/2021 | 10/03/2021
| 3344556 | 10/03/2021 | null

You can see in the example, that the previous review date for the user on row 1 will be the same users review date on row number 2

I have tried using the below:

select user, lead(review_date) over order(order by user,review_date desc) as Previous_review_date

this code works until I need it to be a null value in which case it will simply add the previous review date from an unrelated user.

Any help would be greatly appreciated.

CodePudding user response：

you need to partition the data to identify the lead value

select user, lead(review_date) over order(partition by user order by review_date desc) as Previous_review_date

CodePudding user response：

Pretty sure OUTER APPLY would work here as well using a limit.

Note this could be useful if you need more than just a single column of data.

some docs - outer apply

ask tom - LINQ, cross/outer apply

In essence outer apply will run sub query once for each row in table A correlating the results between the two. Since we limit and order the results; we'll only get 1 record back whose review date is less than the review date. Now as an outer, we keep all records from A and only show results from Z when they exist. So the Z.review_date will be null when no such date/user can be correlated.

SELECT A.user, A.Review_date Z.review_date as Previous_review_Date
FROM TABLE A
OUTER APPLY (SELECT review_date 
             FROM Table B on A.User=B.User and B.Review_date < a.Review_Date 
             ORDER BY review_Date Desc 
             FETCH FIRST 1 ROWS ONLY) Z

Depending on volumn of data one approach vs the other can be more efficient. (See ask tom article)

Using your current approach:

SELECT A.user, A.Review_Date, lead(A.Review_date) over (partition by A.User ORDER BY A.Review_Date DESC) FROM TABLE A

The reason your's isn't working is because it's ordering ALL records by date; not those specific to a user. So you need to "partition" the data to each user and only order that users' review dates.