Teradata - calculate KPI every month for last 24 months

Analytics
Enthusiast

Teradata - calculate KPI every month for last 24 months

Hello

At the end of every month I need to create a report calculating some KPIs for the last 24 months. For example, for april 2016, calculate KPI1, KPI2,KPI3 etc from '2016-04-30' to 2014-05-01'. Again for May 2016, calculate KPIs from '2016-05-31' to '2014-06-01' and so on.

Is there a way to do this in teradata recursively? Thanks.


5 REPLIES
Senior Apprentice

Re: Teradata - calculate KPI every month for last 24 months

Can you rollup the data into one row per month and then simply apply OLAP-functions like 

SUM(col) OVER (ORDER BY year_month ROWS 23 PRECEDING)

In Standard SQL there's RANGE, too, but Teradata only supports ROWS.

Enthusiast

Re: Teradata - calculate KPI every month for last 24 months

These are the separate KPI calculating queries for a single month. How to implement it for every month.

/*1*/

CREATE VOLATILE TABLE CUST_STAT AS (

SELECT cust_id , ctry_iso2_cd ,

CASE 

WHEN STATUS='MEMBER' AND LYLTY_PRG_TIER_CD IN ('PL','VP','PS','RG','FG','PC') AND EXPIRE_FLAG='N' THEN 'MEM'

ELSE 'NONMEM' END STATUS2,

CASE WHEN STATUS2='MEM' AND LYLTY_PRG_TIER_CD IN ('PL','VP','PS') THEN 'PL'

WHEN STATUS2='MEM' THEN LYLTY_PRG_TIER_CD

ELSE 'NM' END TIER

FROM CUST 

WHERE region = 'EM')

WITH DATA ON COMMIT PRESERVE ROWS

;

/*2*/

CREATE VOLATILE TABLE KPI AS (

SELECT

    TIER,

    CU.ctry_iso2_cd,

    COUNT(DISTINCT(CU.CUST_ID)) AS NUM_CUST,

    SUM(CASE  WHEN RMAX12.CUST_ID IS NOT NULL THEN 1 ELSE 0 END) AS ACTIVE_12,

    SUM(CASE  WHEN (RMAX12.CUST_ID IS NOT NULL OR RMAX24.CUST_ID IS NOT NULL)

                                            THEN 1 ELSE 0 END) AS ACTIVE_24,

    SUM(CASE  WHEN (RMAX12.CUST_ID IS NOT NULL AND RMAX24.CUST_ID IS NOT NULL)

                                            THEN 1 ELSE 0 END) AS RETAINED

--( CAST ( RETAINED AS DECIMAL (18,8)) / NULLIFZERO(CAST (ACTIVE_24 AS DECIMAL(18,8)) ))AS RETN_RATE

    FROM CUST_STAT CU

        LEFT JOIN

            (SELECT CUST_ID, MAX(CHKIN_DT) AS MAX_DT

            FROM LAB_BRIERLEY_CI.CUST_RA_36 R36 

            WHERE CHKIN_DT BETWEEN '2015-05-01' AND '2016-04-30'

            GROUP BY 1

            ) RMAX12           

           ON CU.CUST_ID = RMAX12.CUST_ID

           LEFT JOIN

            (SELECT CUST_ID, MAX(CHKIN_DT) AS MAX_DT

            FROM LAB_BRIERLEY_CI.CUST_RA_36 R36 

            WHERE CHKIN_DT BETWEEN '2014-05-01' AND '2015-04-30'

            GROUP BY 1

            ) RMAX24           

           ON CU.CUST_ID = RMAX24.CUST_ID

    GROUP BY 1,2

    )WITH DATA ON COMMIT PRESERVE ROWS

    ;    

/*3*/

SELECT TIER,Ctry_iso2_cd,( ( CAST ( RETAINED AS DECIMAL (18,8)) / NULLIFZERO(CAST (ACTIVE_24 AS DECIMAL(18,8)) )) *100) RETN_RATE

    FROM KPI

    GROUP BY 1,2,3;


Senior Apprentice

Re: Teradata - calculate KPI every month for last 24 months

Ouch, this might be possible, but would be quite hard to implement as a single query for 24 months.

But it's a one time only task to create the initial 24 months, after that you run the query only once a month.

You cOULD combine /*2*/ and /*3*/ into a single Insert/Select and use it within a Stored Procedure to loop over 24 months.

Enthusiast

Re: Teradata - calculate KPI every month for last 24 months

My suggestion is to put all the queries in a single macro that will take as input a DATE variable. Let us call this variable as first_day, when you run for April 2016 this variable should have value of date '2016-04-01'. In the macro replace '2016-04-30' with ADD_MONTHS(:first_day, 1) - 1, '2014-05-01' with ADD_MONTHS(:first_day, -11), '2015-04-30' with ADD_MONTHS(:first_day, -11) - 1 and '2014-05-01' with ADD_MONTHS(:first_day, -23).
Hope this helps.
Enthusiast

Re: Teradata - calculate KPI every month for last 24 months

Thanks!