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

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 ?

CodePudding user response：

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;