SQL Interview Questions – New Users Daily Count

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.