'sql server, find customers with 90 days gap between records [duplicate]

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
sql-server


Solution 1:[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;

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 GuidoG

Related Questions