New Users Daily Count Problem


Description

LeetCode Problem 1107.

Table: Traffic

1
2
3
4
5
6
7
8
9
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| activity      | enum    |
| activity_date | date    |
+---------------+---------+
There is no primary key for this table, it may have duplicate rows.
The activity column is an ENUM type of ('login', 'logout', 'jobs', 'groups', 'homepage').

Write an SQL query that 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.

The query result format is in the following example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Traffic table:
+---------+----------+---------------+
| user_id | activity | activity_date |
+---------+----------+---------------+
| 1       | login    | 2019-05-01    |
| 1       | homepage | 2019-05-01    |
| 1       | logout   | 2019-05-01    |
| 2       | login    | 2019-06-21    |
| 2       | logout   | 2019-06-21    |
| 3       | login    | 2019-01-01    |
| 3       | jobs     | 2019-01-01    |
| 3       | logout   | 2019-01-01    |
| 4       | login    | 2019-06-21    |
| 4       | groups   | 2019-06-21    |
| 4       | logout   | 2019-06-21    |
| 5       | login    | 2019-03-01    |
| 5       | logout   | 2019-03-01    |
| 5       | login    | 2019-06-21    |
| 5       | logout   | 2019-06-21    |
+---------+----------+---------------+

Result table:
+------------+-------------+
| login_date | user_count  |
+------------+-------------+
| 2019-05-01 | 1           |
| 2019-06-21 | 2           |
+------------+-------------+
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.


MySQL Solution

1
2
3
4
5
6
7
select mindate as login_date, count(user_id) as user_count
from (select user_id, min(activity_date) as mindate
        from traffic 
        where activity = 'login'
        group by user_id) t
where datediff('2019-06-30', mindate) <= 90
group by mindate




Related Posts

Game Play Analysis V Problem

LeetCode 1097. Write an SQL query that reports for each...

Unpopular Books Problem

LeetCode 1098. Write an SQL query that reports the books...

New Users Daily Count Problem

LeetCode 1107. Write an SQL query that reports for every...

Sales Analysis III Problem

LeetCode 1084. Write an SQL query that reports the products...

Sales Analysis II Problem

LeetCode 1083. Write an SQL query that reports the buyers...

Sales Analysis I Problem

LeetCode 1082. Write an SQL query that reports the best...