-1

I have a table with multiple records per subsriberid, i need a query to find all subscriberid with 90 days gaps between any two records (grouped by subscriberid).

There are many entries per subscriberid on different dates. The objective to find subscriberid with gaps of 90 days, those who did not have any activity for 90 days in a row.

Desired outcome is a list of subscriberid that were idle for 90 days straight at any given point in time, not necessarily the last 90 days.

The columns in the table are: subscriberid datecreated eventtype (this has the different event types, subscription, unsubscription, charging, basically everything)

select * from SubsEvents
where DIFFERENCE between DateCreated >= 90 DAY
GROUP BY SubscriberId
Nazz Testing
  • 1
  • 2

2 Answers2

1

We can use the function LAG() to compare the date with the date of the previous record.

WITH cte AS (
SELECT
subscriberid,
DATEDIFF(
  d,
  DateCreated,
  LAG(DateCreated) OVER (PARTITION BY subscriberid ORDER BY DateCreated )
  ) AS date_lag
from SubsEvents)
SELECT 
subscriberid
FROM cte
WHERE date_lag >= 90;
1

From the little information you give us, I assume you want this

select t.subscriberid
from   ( select t.subscriberid,
                t.datecreated,
                lag(t.datecreated) over (partition by t.subscriberid order by t.subscriberid, t.datecreated) prevdate
         from   atable t
       ) t
where datediff(day, prevdate, datecreated) >= 90

See this DBFiddle to check if this is what you want

GuidoG
  • 11,359
  • 6
  • 44
  • 79
  • I apologize if the data is too little as i am not a developer. I am just a user trying to extract data from this table. table name ssubevents columns available: subscriberid datecreated eventtype (this has the different event types, subscription, unsubscription, charging, basically everything). Ideally i need to identify subscribers who do no have any records for a period of 90 days straight. – Nazz Testing May 02 '22 at 12:33
  • @NazzTesting What do you mean with the data is too little ? You have not shown any data at all so we just have to improvize some data ourself. If you want quality answers than provide us with more information and data – GuidoG May 02 '22 at 12:36
  • I am happy to give any further data, what else is needed? – Nazz Testing May 02 '22 at 12:40
  • We need data and we need to see what you expect as result – GuidoG May 02 '22 at 12:46
  • @NazzTesting It would be best if you would use the DBFidlle link in the answer to add your data – GuidoG May 02 '22 at 12:47