How to check active IDs from six months ago

Question

How can I sum(ctc) in one line with a single statement that IDs from 1/1/2023 still exist on 6/1/2023? so I tried the below statement but returned 0!

SUM( CASE WHEN ID = LAG(ID) OVER (PARTITION BY GP ORDER BY Date- 5 MONTH) 
THEN SUM(FLOAT(value)) ELSE 0 END) AS count

I also tried this :

case when date = Date- 5 MONTH then 
        coalesce(
            lag(value) over(
                partition by id, case when date = eDate- 5 MONTH then 1 else 0 end
                order by date
            ),
            0
        )
    end  AS PrevValue

dbfiddle Link to sample data!

Answer 1

Use a correlated subquery like here:

WITH CUSTOMERS AS 
(
SELECT GP, ID, DATE(TO_DATE(DATE, 'MM/DD/YYYY')) AS DATE, CTC
FROM
(
VALUES
  ('AAAA', '001', '1/1/2023', 1)
, ('AAAA', '002', '1/1/2023', 1)
, ('AAAA', '003', '1/1/2023', 1)
, ('AAAA', '004', '1/1/2023', 0)
, ('AAAA', '001', '6/1/2023', 1)
, ('AAAA', '002', '6/1/2023', 1)
, ('AAAA', '005', '6/1/2023', 1)
, ('AAAA', '006', '6/1/2023', 1)
) CUSTOMERS (GP, ID, DATE, CTC)
)
SELECT 
  A.*
, A.CTC +
  COALESCE 
  (
    (
      SELECT SUM(B.CTC) 
      FROM CUSTOMERS B 
      WHERE B.ID = A.ID AND B.DATE = A.DATE - 5 MONTH
     )
   , 0
  ) AS CTC_SUM
FROM CUSTOMERS A

GP	ID	DATE	CTC	CTC_SUM
AAAA	001	2023-01-01	1	1
AAAA	002	2023-01-01	1	1
AAAA	003	2023-01-01	1	1
AAAA	004	2023-01-01	0	0
AAAA	001	2023-06-01	1	2
AAAA	002	2023-06-01	1	2
AAAA	005	2023-06-01	1	1
AAAA	006	2023-06-01	1	1

fiddle