Get Last N Month Data For Each Account

Question

I don't know if this is possible or not, but can we get last N month data for each account / ID respectively?

Say I have a table that contains ID, transaction amount, and date. Each ID has their last date differ from each other

ID    | Trx_Amount |    Trx_Date
ID001 |    104     |   04/10/2022
ID002 |     76     |   20/09/2022
ID003 |     82     |   17/08/2022

Then I want to get their transaction for the last 3 months from their last transaction date respectively, not just using WHERE Trx_Date BETWEEN DATE(CURRENT DATE) - 3 MONTHS AND DATE(CURRENT DATE). Is it possible?

Answer 1

WITH MYTAB (ID, Trx_Amount, Trx_Date) AS
(
  VALUES
    ('ID001', 30, '2022-10-04'::DATE)
  , ('ID001', 20, '2022-10-04'::DATE - 3 MONTH)
  , ('ID001', 10, '2022-10-04'::DATE - 3 MONTH - 1)
  , ('ID002',  3, '2022-10-01'::DATE)
  , ('ID002',  2, '2022-10-01'::DATE - 3 MONTH)
  , ('ID002',  1, '2022-10-01'::DATE - 3 MONTH - 1)
)
SELECT T.*
FROM
(
  SELECT ID, MAX (Trx_Date) AS Trx_Date
  FROM MYTAB
  GROUP BY ID
) G
JOIN MYTAB T ON T.ID = G.ID AND T.Trx_Date BETWEEN G.Trx_Date - 3 MONTH AND G.Trx_Date
ORDER BY T.ID, T.Trx_Date DESC

ID	TRX_AMOUNT	TRX_DATE
ID001	30	2022-10-04
ID001	20	2022-07-04
ID002	3	2022-10-01
ID002	2	2022-07-01