Sql query to calculate active count for aparticular date of every month

I have data for the user regarding his subscription transaction date.

suppose if the user has a monthly subscription and the subscription date in on 4th of every month.
Then how can I count that user as an active user for 4th of every month

Subscription Date is auto-debit transaction date by the user.

Data Available:Screenshot 2019-12-09 at 1.46.03 PM

Data Required:
Screenshot 2019-12-09 at 1.44.26 PM

Since subscription begins from October.

What kind of visualization do you intend to build with this info? Normally you wouldn’t need to project subscriptions in this way. Instead you would SELECT data from your transactions table.

Actually what I need is to show daily active user count.

If I have the following data:

Screenshot 2019-12-10 at 12.48.05 PM

Then the result I want is:

Screenshot 2019-12-10 at 12.48.20 PM

That’s a very strange data layout :man_shrugging:

But in most SQLs you can do this as follows:

SELECT subscriptionDate, COUNT(*) FROM ... GROUP BY 1

And then augment your subscriptionDate with a datetime function.