Count the total number of records before a specific day for the last N days in SQL

I have a table that stores customer information including their registration date with the service. I would like to know the total number of subscribed customers (from the beginning of the service up until that day) at the end of the day for the past 10 days.

I imagine the solution would be something similar to a for loop, so the best I managed so far is the following:

DECLARE @Counter INT 
SET @Counter=1
WHILE ( @Counter <= 10)
BEGIN
   select try_convert(date, getdate()-@Counter), count(id)
   from Table_name
   where try_convert(date, registrationdate) <=  try_convert(date, getdate()-@Counter)
    SET @Counter  = @Counter  + 1
END

The problem with the above script is that it produces 10 different tables and I haven’t been able to union them together. I’m fairly new to SQL so there might be a simpler solution that I’m missing!

The output I have in mind looks like this:

date	total number registered
2022-01-30	500,000
2022-01-29	499,800

Answer

It has nothing to do with a loop. You would simply create an SQL select that selects them. ie:

select * 
from myTable 
where datediff(day, registrationDate, getdate()) <= 10;

EDIT: Question is edited to show the desired output now, then it would need a different way like:

select registrationDate, count(*) as TotalNumberRegistered 
    from myTable 
    where datediff(day, registrationDate, getdate()) <= 10
    group by registrationDate;

EDIT: If as P.Salmon said you were after a “running total” then you still can get it with a slight modification to code:

WITH dailyTotals(registrationDate, TotalNumberRegistered) AS 
     (
        SELECT   registrationDate, COUNT(*)
        FROM     myTable
        WHERE    DATEDIFF(DAY, registrationDate, GETDATE())<=10
        GROUP BY registrationDate
     )
SELECT registrationDate
   , SUM(TotalNumberRegistered) OVER (ORDER BY registrationDate) AS TotalNumberRegistered
FROM dailyTotals
Order by registrationDate desc; -- you want it to be ordered last date first? 

Here is DBFiddle demo