How to calculate average monthly number of some events in MS SQL Server?

I have table in MS SQL Server like below:

ID_EVENT | PRODUCT| DATE
-------------------------------
123      | A      | 2021-01-15
456      | A      | 2021-01-22
789      | A      | 2021-02-05
110      | B      | 2021-01-18
124      | B      | 2021-02-11

I need to calculate average monthly (for January and February) number of ID_EVENT per PRODUCT. So as a result I need something like below:

PRODUKT | AVG_PER_MNTH
-----------------
A       | 1.5 
B       | 1

• A = 1.5 because 3 / 2 = 1.5 --> (number of ID_EVENT for January + number of ID_EVENT for February) / number of months which we analyse (2 -> January and February)
• B = 1 because 1 / 1 = 1 --> (number of ID_EVENT for January + number of ID_EVENT for February) / number of months which we analyse (2 -> January and February)

How can I do that in MS SQL Server ?

Solution 1:

One option, aggregating first by month and year, then by product:

WITH cte AS (
    SELECT PRODUCT, 1.0*COUNT(*) AS cnt
    FROM yourTable
    GROUP BY PRODUCT, FORMAT(DATE, 'MM.yyyy')
)

SELECT PRODUCT, AVG(cnt) AS AVG_PER_MNTH
FROM cte
GROUP BY PRODUCT;

Demo

Solution 2:

WITH CTE AS 
(SELECT PRODUCT
        ,COUNT(*) ac cnt,
        ,DATEADD(month, DATEDIFF(month, 0, Date), 0) AS StartOfMonth
 FROM YourTable
 GROUP BY PRODUCT
        ,DATEADD(month, DATEDIFF(month, 0, Date), 0)
) 
SELECT 
  PRODUCT
  ,AVG(cnt) as MonthlyAverage
FROM CTE
GROUP BY PRODUCT