CODEWITHSUNDEEP
sql
newleaf
newleaf

Reputation: 2457

Postgres SQL for loop to count monthly existing users

I have a users database for user sign up time:

id,  signup_time
100    2020-09-01
001    2018-01-01
....

How could I find monthly existing user for all the history record? Use the last day in the month as the cut off day, existing users means if I observe in July last day, 2020-07-31, this user had already signed up before 2020-07-01. If I observe in June last day 2020-06-30, this user had already signed up before 2020-06-01.

Similar as a for loop in other language:

observation_year_month_list = ['2020-04','2020-05','2020-06']

 for i in observation_year_month_list:
      
     if user signup_time  < i:
        monthly_existing_user_count+1

Upvotes: 0

Views: 68

Answers (1)

Schwern
Schwern

Reputation: 164799

While PL/SQL has loops, that is a procedural language extension. SQL is a declarative language and does not use loops. Instead, you describe the results you want and the database comes up with a query plan to make it happen.

Your case is handled by group by to aggregate rows into groups. In this case by month using date_trunc. Then you use the aggregate function count to count up how many users are in each group.

select
  count(id) as num_users,
  date_trunc('month', signup_time) as signup_month
from users
group by date_trunc('month', signup_time)

Upvotes: 1

Related Questions