sqlaverageteradatateradata-sql-assistant

How to calculate average number of actions in selected month per client in Teradata SQL?


I have table with transactions in Teradata SQL like below:

ID   | trans_date
-------------------
123  | 2021-09-15
456  | 2021-10-20
777  | 2021-11-02
890  | 2021-02-14
...  | ...

And I need to calculate average number of transactions made by clients in month: 09, 10 and 11, so as a result I need something like below:

Month  | Avg_num_trx
--------------------------------------------------------
09     | *average number of transactions per client in month 09*
10     | *average number of transactions per client in month 10*
11     | *average number of transactions per client in month 11*

How can I do taht in Teradata SQL ?


Solution

  • Not as familiar with Teradata, you could probably start by extracting the month from the trans_date, then grouping id and month and adding in count(id). From there you could group month by avg(count_id). Something like this -

    WITH extraction AS(
    SELECT 
     ID,
     EXTRACT (MONTH FROM trans_date) AS MM
    FROM your_table)
    
    ,
    
    WITH id_counter AS(
    SELECT
     ID,
     MM,
     COUNT(ID) as id_count
    FROM extraction
    GROUP BY ID, MM)
    
    SELECT
     MM,
     AVG(id_count) AS Avg_num_trx
    FROM id_counter
    ORDER BY MM;
    

    The first CTE grabs month from trans_date. The second CTE groups ID and month with count(ID) - should give you the total actions in that month for that client ID as id_count. The final table gets the average of id_count grouped by month, which should be the average interactions per client for the period.

    If EXTRACT doesn't work for some reason you could also try STRTOK(trans_date, '-', 2).

    Other potential methods to replace -

    --current
    EXTRACT (MONTH FROM trans_date) AS MM
    
    --option 1
    STRTOK(trans_date, '-', 2) AS MM
    
    --option 2
    LEFT(RIGHT(trans_date, 5),2) AS MM
    

    Above reworked as subqueries - should help with debugging -

    SELECT
     MM,
     AVG(id_count) AS Avg_num_trx
    FROM (SELECT
           ID,
           MM,
           COUNT(ID) as id_count
           FROM (SELECT 
                  ID,
                  EXTRACT (MONTH FROM trans_date) AS MM
                  FROM your_table) AS a
           GROUP BY ID, MM) AS b
    ORDER BY MM;