CODEWITHSUNDEEP
sqlpostgresqldate
user8545255
user8545255

Reputation: 839

Retrieve records that are in the date ranges in PostgreSQL

For each customer, I am trying to retrieve the records that are within 45 days of the most recent submit_date.

customer  submit_date   salary
      A   2019-12-31   10000
      B   2019-01-01   12000
      A   2017-11-02   11000
      A   2019-03-03   3000
      B   2019-03-04   5500
      C   2019-01-05   6750
      D   2019-02-06   12256
      E   2019-01-07   11345
      F   2019-01-08   12345

Upvotes: 0

Views: 26

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

I am inclined to try:

select t.*
from t
where t.submit_date >= (select max(t2.submit_date) - interval '45 day'
                        from t t2
                       );

I think this can very much take advantage of an index on (submit_date).

If you want this relative to each customer, use a correlation clause:

select t.*
from t
where t.submit_date >= (select max(t2.submit_date) - interval '45 day'
                        from t t2
                        where t2.customer = t.customer
                       );

This wants an index on (customer, submit_date).

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246238

Window functions come to the rescue:

SELECT customer, submit_date, salary
FROM (SELECT customer, submit_date, salary,
             max(submit_date) OVER (PARTITION BY customer) AS latest_date
      FROM thetable) AS q
WHERE submit_date >= latest_date - 45;

Upvotes: 1

Related Questions