CODEWITHSUNDEEP
sqlpostgresqlwindow-functions
Hang Nguyen
Hang Nguyen

Reputation: 57

How to select the previous date with a condition

I have a task that requires selecting the previous top-up date done by the same id_user with a promo_id = Y. I'm still a beginner in SQL so I found it struggle to finish this task.

I tried to use LAG() window function but don't know how to change the offset value to a variable instead of a number.

Thank you so much!

Table topups

 id_user |    date    | promo_id
---------+------------+----------
    1    | 2017-06-20 |    N
    1    | 2017-05-20 |    N
    1    | 2017-04-20 |    Y
    1    | 2017-03-20 |    Y
    1    | 2017-02-20 |    N

Expected result

 id_user |    date    | promo_id | prv_qual_topup_dt
---------+------------+----------+-------------------
    1    | 2017-06-20 |     N    |    2017-04-20
    1    | 2017-05-20 |     N    |    2017-04-20
    1    | 2017-04-20 |     Y    |    2017-03-20
    1    | 2017-03-20 |     Y    |       NULL
    1    | 2017-02-20 |     N    |       NULL

Upvotes: 1

Views: 805

Answers (1)

Jim Jones
Jim Jones

Reputation: 19693

In this case, perhaps a subquery would be simpler than window functions:

SELECT *,(
 SELECT max(date) FROM topups q2 
 WHERE id_user = q.id_user AND 
       q2.promo_id = 'Y' AND 
       q.date > q2.date)
FROM topups q ORDER BY date DESC;

 id_user |    date    | promo_id |    max     
---------+------------+----------+------------
       1 | 2017-06-20 | N        | 2017-04-20
       1 | 2017-05-20 | N        | 2017-04-20
       1 | 2017-04-20 | Y        | 2017-03-20
       1 | 2017-03-20 | Y        | 
       1 | 2017-02-20 | N        | 
(5 rows)

Demo: db<>fiddle

Upvotes: 1

Related Questions