By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Orders
( CustomerID int
, OrderID int
, OrderDate date
);
INSERT INTO Orders VALUES (1, 11, '2020-01-01');
INSERT INTO Orders VALUES (1, 12, '2020-02-01');
INSERT INTO Orders VALUES (1, 13, '2021-01-01');
INSERT INTO Orders VALUES (2, 24, '2020-01-01');
INSERT INTO Orders VALUES (2, 25, '2020-02-01');
SELECT CustomerID, YEAR(OrderDate) AS OrderYear, COUNT(*) AS OrderCount
FROM Orders
GROUP BY CustomerID, YEAR(OrderDate);
| CustomerID | OrderYear | OrderCount |
|---|---|---|
| 1 | 2020 | 2 |
| 1 | 2021 | 1 |
| 2 | 2020 | 2 |
SELECT OrderYear, AVG(OrderCount) AS AvgOrdersPerCustomer
FROM
(
SELECT CustomerID, YEAR(OrderDate) AS OrderYear, COUNT(*) AS OrderCount
FROM Orders
GROUP BY CustomerID, YEAR(OrderDate)
) o
GROUP BY OrderYear;
| OrderYear | AvgOrdersPerCustomer |
|---|---|
| 2020 | 2.0000 |
| 2021 | 1.0000 |