SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dbo.CustomerStable
(
  CREATEDBY varchar(32),
  CREATEDAT datetime,
);

INSERT dbo.CustomerStable(CREATEDBY, CREATEDAT)
  VALUES('Adam', '20210615'), -- not included due to date
        ('Adam', '20210618'), -- included in June
        ('Adam', '20210712'), -- included in June
        ('Adam', '20210819'), -- included in August
        ('Fran', '20210619'), -- not included because whodis?
        ('John', '20210618'), -- included in June
        ('John', '20210627'), -- included in June
        ('John', '20210630'), -- included in June
        ('John', '20210727'), -- included in July
        ('John', '20210730'), -- included in July
        ('John', '20210901'), -- included in August
        ('Molly','20210624'), -- included in June
        ('Molly','20210730'), -- included in July
        ('Molly','20210826'), -- included in August
        ('Molly','20210901'); -- included in August

DECLARE @FirstDate  date = '20210617',
            @numMonths  tinyint = 3;
    
    ;WITH n (n) AS 
    (
      SELECT 1 UNION ALL SELECT n+1 FROM n WHERE n < @numMonths
    ),
    d (MonthNumber, d) AS 
    (
      SELECT n, DATEADD(MONTH, n.n-1, @FirstDate) FROM n
    ),
    src AS
    (
      SELECT c.CREATEDBY, MonthNumber, num = COUNT(*)
        FROM dbo.CustomerStable AS c INNER JOIN d 
          ON c.CREATEDAT >= d.d AND c.CREATEDAT < DATEADD(MONTH, 1, d.d)
        WHERE c.CREATEDBY IN ('Adam', 'John', 'Molly')
        GROUP BY c.CREATEDBY, d.MonthNumber
    )
    SELECT CREATEDBY,
      FIRSTMONTH = COALESCE([1],0),
      SECONDMONTH = COALESCE([2],0),
      THIRDMONTH = COALESCE([3],0)
    FROM src PIVOT (MAX(num) FOR MonthNumber IN ([1],[2],[3])) AS p;

CREATEDBY	FIRSTMONTH	SECONDMONTH	THIRDMONTH
Adam	2	0	1
John	3	2	1
Molly	1	1	2