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

CodePudding user response：

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)