How to select the previous date with a condition

Question

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

Have a look at [Conditional lead/lag function PostgreSQL?](https://stackoverflow.com/q/41796929/1048572) - apparently, since Postgres 13 you can just use `LAG(date) OVER (ORDER BY date) FILTER (WHERE promo_id = 'Y')` — Bergi, Jun 29 '21 at 11:53
@Bergi is there already `filter` support for pure window functions (not aggregate)? — Jim Jones, Jun 29 '21 at 12:11
@JimJones I don't know, but the answer to the linked question says "*`FILTER` is not implemented for pure genuine functions like `lead()` or `lag()` (up to Postgres 13)*", and I'd trust Erwin on that. — Bergi, Jun 29 '21 at 12:17
@Bergi I see. He might be mistaken (which surprises me).. my pg13.3 says *"ERROR: FILTER is not implemented for non-aggregate window functions"*. For a moment I got excited :-D — Jim Jones, Jun 29 '21 at 12:20
@Bergi I tried to add the FILTER but there's the error that Jim Jones pointed out. — Hang Nguyen, Jun 29 '21 at 12:42

score 1 · Accepted Answer · answered Jun 29 '21 at 13:08

1

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

answered Jun 29 '21 at 13:08

Jim Jones

18,404
3
35
44

Thank you! Are `q` and `q2` the aliases for the topups table? – Hang Nguyen Jun 29 '21 at 16:15
How can I reuse this table? I can't add `WITH table AS` to the beginning of this query. – Hang Nguyen Jun 29 '21 at 17:30
1

What do you mean by reuse the table? – Jim Jones Jun 29 '21 at 18:14
Hi, I want to nest this SELECT statement inside another SELECT statement using `WITH table_name AS` but there's an error because of the conflict with the alias `q`. Sorry, I'm new to SQL and haven't use alias much. – Hang Nguyen Jun 29 '21 at 18:52
1

@HangNguyen I see. You should post a new question.. otherwise it gets pretty hard to fully understand your problem. – Jim Jones Jun 29 '21 at 18:54

How to select the previous date with a condition

1 Answers1