Description

Table: Traffic

Column NameType
user_idint
activityenum
activity_datedate
  • This table may have duplicate rows.
  • The activity column is an ENUM (category) type of (’login’, ’logout’, ‘jobs’, ‘groups’, ‘homepage’).

Problem Statement

Write a solution 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 result format is in the following example.

Example 1:

Input:

  • Traffic table:
user_idactivityactivity_date
1login2019-05-01
1homepage2019-05-01
1logout2019-05-01
2login2019-06-21
2logout2019-06-21
3login2019-01-01
3jobs2019-01-01
3logout2019-01-01
4login2019-06-21
4groups2019-06-21
4logout2019-06-21
5login2019-03-01
5logout2019-03-01
5login2019-06-21
5logout2019-06-21

Output:

login_dateuser_count
2019-05-011
2019-06-212

Explanation:

  • Note that we only care about dates with non zero user count.
  • The user with id 5 first logged in on 2019-03-01 so he’s not counted on 2019-06-21.

Solution

The problem is asking to find the number of users who had first login on each date in the last 90 days. The problem can be divided into following parts.

  1. Find the first login date for each user.

For this, you can use ROW_NUMBER() window function and you filter the rows where row_number = 1 to get first event to find the first_login.

1WITH first_logins AS (
2    SELECT user_id, activity_date AS first_login_date,
3        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date) rn
4        FROM Traffic
5        WHERE activity = 'login'
6) SELECT user_id, first_login_date 
7    FROM first_logins
8    WHERE rn = 1;

Another approach to find first_login_date is using MIN() function.

1WITH first_logins AS (
2    SELECT user_id, MIN(activity_date) first_login_date
3        FROM Traffic
4        WHERE activity='login'
5        GROUP BY user_id
6        HAVING first_login_date BETWEEN DATE_SUB('2019-06-30', INTERVAL 90 DAY) AND '2019-06-30'
7) SELECT user_id, first_login_date
8    FROM first_logins;
  1. Once you’ve found the first_logins temporary view, you need to aggregate number of users per date in the last 90 days. To find the users who have logged in first time in the last 90 days, you can use DATE_SUB('2019-06-30', INTERVAL 90 DAY).
1SELECT first_login_date login_date, COUNT(1) user_count
2    FROM first_logins
3    WHERE rn = 1 
4        AND first_login_date >= DATE_SUB('2019-06-30', INTERVAL 90 DAY)
5    GROUP BY login_date;

The final solution looks like this.

1WITH first_logins AS (
2    SELECT user_id, activity_date AS first_login_date,
3        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date) rn
4        FROM Traffic
5        WHERE activity = 'login'
6) SELECT first_login_date login_date, COUNT(1) user_count
7    FROM first_logins
8    WHERE rn = 1 AND first_login_date >= DATE_SUB('2019-06-30', INTERVAL 90 DAY)
9    GROUP BY login_date;

Another alternative using MIN() function is as below.

1WITH first_logins AS (
2    SELECT user_id, MIN(activity_date) first_login_date
3        FROM Traffic
4        WHERE activity='login'
5        GROUP BY user_id
6        HAVING first_login_date BETWEEN DATE_SUB('2019-06-30', INTERVAL 90 DAY) AND '2019-06-30'
7) SELECT first_login_date login_date, COUNT(1) user_count
8    FROM first_logins
9    GROUP BY login_date;