Count the Max Continuous Inactive Days by SQL

UDA
Enthusiast

Count the Max Continuous Inactive Days by SQL

Dear whom know the solution,
Currently I need to count the maximum inactive days by subscriber Id, by give the example below, I need to count the inactive days (continuously) of the subscriber, and pick the maximum value.

Subs_ID Date Flag(0=Active, 1=Inactive)
60161220001 1-Feb-07 0
60161220001 2-Feb-07 0
60161220001 3-Feb-07 0
60161220001 4-Feb-07 0
60161220001 5-Feb-07 1
60161220001 6-Feb-07 1
60161220001 7-Feb-07 1
60161220001 8-Feb-07 1
60161220001 9-Feb-07 0
60161220001 10-Feb-07 0
60161220001 11-Feb-07 1
60161220001 12-Feb-07 1
60161220001 13-Feb-07 1
60161220001 14-Feb-07 1
60161220001 15-Feb-07 1
60161220001 16-Feb-07 0
60161220001 17-Feb-07 0
60161220001 18-Feb-07 1
60161220001 19-Feb-07 1

Thus, the subscriber above having the inactive days (continuously) are
4 days, 5 days and 2 days.
I need the max inactive days (5 days) for this subscriber.

How should I do the calculation by using Teradata SQL?

Thanks.
Benjamin
7 REPLIES
Enthusiast

Re: Count the Max Continuous Inactive Days by SQL

This wasn't as straightforward as I thought it might be, but I think that this will do it:

SELECT subs_id
,MAX(days_running)
FROM (
SELECT subs_id
,CASE WHEN next_end_date is NOT NULL
THEN next_end_date - start_date + 1
ELSE NULL
END days_running
,MAX(end_date) OVER (PARTITION BY subs_id
ORDER BY subs_date
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) next_end_date
FROM (
SELECT subs_id
,subs_date
,CASE WHEN subs_date = MAX(subs_date) OVER(PARTITION BY subs_id
ORDER BY subs_date
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) + 1
THEN NULL
ELSE subs_date
END start_date
,CASE WHEN subs_date = MAX(subs_date) OVER(PARTITION BY subs_id
ORDER BY subs_date
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) - 1
THEN NULL
ELSE subs_date
END end_date
FROM barry.test_tbl
WHERE flag = 1
QUALIFY start_date IS NOT NULL
OR end_date IS NOT NULL) DT1
) DT2
GROUP BY 1;

I used "subs_date" in place of your "date" column. There might be a better/easier way to do this.
Enthusiast

Re: Count the Max Continuous Inactive Days by SQL

I noticed after I sent the last message that the solution I posted would not work if you only had one day as a max. So, here is an "adjusted" solution that should work in that case as well:

SELECT subs_id
,MAX(days_running)
FROM (
SELECT subs_id
,CASE WHEN next_end_date is NOT NULL
THEN next_end_date - start_date + 1
ELSE NULL
END days_running
,MAX(end_date) OVER (PARTITION BY subs_id
ORDER BY subs_date
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) next_end_date
FROM (
SELECT subs_id
,subs_date
,CASE WHEN subs_date = MAX(subs_date) OVER(PARTITION BY subs_id
ORDER BY subs_date
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) + 1
THEN NULL
ELSE subs_date
END start_date
,CASE WHEN subs_date = MAX(subs_date) OVER(PARTITION BY subs_id
ORDER BY subs_date
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) - 1
THEN NULL
ELSE subs_date
END end_date
FROM barry.test_tbl
WHERE flag = 1
QUALIFY start_date IS NOT NULL
OR end_date IS NOT NULL) DT1
) DT2
GROUP BY 1;
Enthusiast

Re: Count the Max Continuous Inactive Days by SQL

Thanks a lot. It work. :-)
Senior Apprentice

Re: Count the Max Continuous Inactive Days by SQL

Hi Benjamin,
if there are no gaps and exactly one row per day:

SELECT
subs_id, flag, COUNT(*)
FROM
(
SELECT
subs_id,
subs_date,
flag,
RANK() OVER (PARTITION BY subs_id, flag ORDER BY subs_date) AS rnk,
subs_date - rnk AS dummy
FROM test
WHERE flag = 1
) dt
GROUP BY subs_id, flag, dummy
QUALIFY
RANK() OVER (PARTITION BY subs_id, flag
ORDER BY COUNT(*) DESC ) = 1

If there are gaps:

SELECT subs_id, COUNT(*)
FROM
(
SELECT
subs_id,
subs_date,
flag,
SUM(CASE WHEN flag = 1 THEN 0 ELSE 1 END)
OVER (PARTITION BY subs_id ORDER BY subs_date
ROWS UNBOUNDED PRECEDING) AS dummy
FROM test
QUALIFY flag = 1
) dt
GROUP BY subs_id, dummy
QUALIFY
RANK () OVER (PARTITION BY subs_id
ORDER BY COUNT(*) DESC) = 1

Dieter
Enthusiast

Re: Count the Max Continuous Inactive Days by SQL

Thanks.

I had run the SQL, it work well when there is only 1 max inactive days for the subscriber.
If the subscriber having 2 or more max inactive days, then the subscriber will having 2 or more records.

Subscriber_Id Flag Max(Running_Days)
60161220014 1 1
60161220015 1 13
60161220016 1 6
60161220016 1 6
60161220017 1 7
60161220018 1 2
60161220018 1 2

What I do is just take 1 records per subscriber. :-)
Thanks again.
Senior Apprentice

Re: Count the Max Continuous Inactive Days by SQL

Hi Benjamin,
use ROW_NUMBER instead of RANK or a nested aggregate or DISTINCT.

Dieter
Enthusiast

Re: Count the Max Continuous Inactive Days by SQL

Thanks Dieter. ^_^