How to calculate average monthly number of some action in some perdion in Teradata SQL?-CodePudding

I have table in Teradata SQL like below:

ID     trans_date
------------------------
123   | 2021-01-01
887   | 2021-01-15
123   | 2021-02-10
45    | 2021-03-11
789   | 2021-10-01
45    | 2021-09-02

And I need to calculate average monthly number of transactions made by customers in a period between 2021-01-01 and 2021-09-01, so client with "ID" = 789 will not be calculated because he made transaction later.

In the first month (01) were 2 transactions In the second month was 1 transaction In the third month was 1 transaction In the nineth month was 1 transactions

So the result should be (2 1 1 1) / 4 = 1.25, isn't is ?

How can I calculate it in Teradata SQL? Of course I showed you sample of my data.

CodePudding user response：

SELECT ID, AVG(txns) FROM
    (SELECT ID, TRUNC(trans_date,'MON') as mth, COUNT(*) as txns
     FROM mytable
     -- WHERE condition matches the question but likely want to
     -- use end date 2021-09-30 or use mth instead of trans_date
     WHERE trans_date BETWEEN date'2021-01-01' and date'2021-09-01' 
     GROUP BY id, mth) mth_txn
GROUP BY id;

CodePudding user response：

Your logic translated to SQL:

        --(2 1 1 1) / 4
SELECT id, COUNT(*) / COUNT(DISTINCT TRUNC(trans_date,'MON')) AS avg_tx
FROM mytable
WHERE trans_date BETWEEN date'2021-01-01' and date'2021-09-01' 
GROUP BY id;

You should compare to Fred's answer to see which is more efficent on your data.