Problem Description –
Write an SQL query to reports for every date within at most
90 days from today, the number of users that logged in for the first time on that date. Assume today is
Return the result table in any order.
The query result format is in the following example.
Difficulty Level – Medium
Problem Link – New users daily count
SELECT login_date, COUNT(*) as user_count FROM ( SELECT user_id, MIN(activity_date) as login_date FROM traffic WHERE activity = 'login' GROUP BY 1 ) x WHERE login_date BETWEEN DATE_SUB('2019-06-30', INTERVAL 90 DAY) AND '2019-06-30' GROUP BY 1 ORDER BY 1
Here we used MIN function to get the first login date of each users and in the outer query we used the where clause to filter only the data that is within 90 days from the given date.