CODEWITHSUNDEEP
sql
Josh
Josh

Reputation: 35

SQL Get Number of Users and Retention Rate

I'm really a newbie in SQL so I want to start learning SQL. Here I have 2 tables:

  1. Users table has these attributes: id, name, phone and created_at
  2. Transactions table has these attributes: id, user_id, amount, created_at

I want to get:

  1. Daily, weekly and monthly active users (active users are users who create at least 1 transaction on that day).
  2. Weekly and monthly retention rate

Do you have any idea how to solve this so I can try?

Thank you

Upvotes: 0

Views: 406

Answers (1)

Zakaria
Zakaria

Reputation: 4806

To get the ids and names of daily active users (users who performed a transaction today) you can try

select id, name 
from users 
where exists (select * from transactions 
              where users.id = transactions.user_id 
                and day(now()) = day(transactions.created_at));

To get the number of daily active users

select count(id) 
from users 
where exists (select * from transactions 
              where users.id = transactions.user_id 
                and day(now()) = day(transactions.created_at));

For the weekly and monthly active users just replace "day" with "month" or "week" in the queries. Example

select count(id) 
from users 
where exists (select * from transactions 
              where users.id = transactions.user_id 
                and month(now()) = month(transactions.created_at));

By retention rates you mean percentage of active users? If so this should work

select 
    1.0 * (select count(id) from users 
           where exists (select * from transactions 
                         where users.id = transactions.user_id 
                           and day(now()) = day(transactions.created_at))) / (select count(id) from users)

Upvotes: 1

Related Questions