SQL to Calculate Customer Tenure/Service Start Date using a Cooldown Period Logic

Introduction

In this article, we will discuss how to calculate the customer tenure with the service provider. The business scenario involves multiple aspects such as oldest account start date, one customer can have more than one active account at a given time, cooldown period is 6 months, if a customer opens an account post 6 months then the tenure calculation happens from the new account open date.

Background

To solve this problem, we will first need to understand the different types of queries that can be used. In this case, we are using SQL queries to solve the problem.

Oldest Account Start Date

The oldest account start date is the earliest date when a customer’s account was active. This date is used as the tenure start date for the customer.

Cooldown Period

The cooldown period is 6 months, which means that if a customer closes an account, they cannot open another account within this time frame. If the customer opens an account after 6 months, then the tenure calculation happens from the new account open date.

Real-World Scenario

We will use the following real-world scenario to demonstrate how to solve this problem:

Suppose we have a table calc_customer_tenure with the following data:

Query

We will use the following SQL query to solve this problem:

WITH cte1 as (
  SELECT customer_id, ACCT_STRT_DT, ACCT_END_DT,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ACCT_STRT_DT) rn
  FROM calc_customer_tenure
), 
cte2 (customer_id, ACCT_STRT_DT, ACCT_END_DT, rn, tenure_start_date, tenure_end_date) AS (
  SELECT customer_id, ACCT_STRT_DT, ACCT_END_DT, rn, 
        ACCT_STRT_DT tenure_start_date,
        ACCT_END_DT tenure_end_date
  FROM cte1
  WHERE rn = 1
  UNION ALL
  SELECT cte1.customer_id, cte1.ACCT_STRT_DT, cte1.ACCT_END_DT, cte1.rn,
         CASE WHEN cte1.ACCT_STRT_DT > ADD_MONTHS(cte2.tenure_end_date, 6)
              THEN cte1.ACCT_STRT_DT
              ELSE cte2.tenure_start_date
              END,
         CASE WHEN cte1.ACCT_STRT_DT > ADD_MONTHS(cte2.tenure_end_date, 6)
              THEN cte1.ACCT_END_DT
              ELSE GREATEST(cte1.ACCT_END_DT, cte2.tenure_end_date)
              END  
  FROM cte1
  JOIN cte2 ON cte1.customer_id = cte2.customer_id AND cte1.rn = cte2.rn + 1 
)
SELECT customer_id, CASE WHEN ADD_MONTHS(NVL(tenure_end_date, SYSDATE), 6) < SYSDATE THEN NULL ELSE tenure_start_date END AS CUSTOMER_TENURE_START_DATE FROM (
SELECT 
  cte2.*, row_number() over (partition by customer_id order by rn desc) as rank_derv
  FROM cte2 ) subset
WHERE rank_derv = 1 
ORDER BY 1,2 ;

Explanation

This query uses recursive common table expressions (CTEs) to solve the problem. The first CTE cte1 assigns a row number to each account start date within each customer group. The second CTE cte2 selects the first account start date and end date for each customer, as well as the subsequent account start dates and end dates based on the cooldown period.

Conclusion

In this article, we discussed how to calculate the customer tenure with the service provider using a cooldown period logic. We used SQL queries to solve the problem and explained the different steps involved in solving the query.

Last modified on 2023-10-31