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 2019-06-30
.
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
Solution –
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.