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 ?
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;