Welcome to Subscribe On Youtube
1107. New Users Daily Count
Description
Table: Traffic
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| activity | enum |
| activity_date | date |
+---------------+---------+
This table may have duplicate rows.
The activity column is an ENUM (category) type of ('login', 'logout', 'jobs', 'groups', 'homepage').
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_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 | +---------+----------+---------------+ Output: +------------+-------------+ | login_date | user_count | +------------+-------------+ | 2019-05-01 | 1 | | 2019-06-21 | 2 | +------------+-------------+ 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.
Solutions
-
# Write your MySQL query statement below WITH T AS ( SELECT user_id, MIN(activity_date) OVER (PARTITION BY user_id) AS login_date FROM Traffic WHERE activity = 'login' ) SELECT login_date, COUNT(DISTINCT user_id) AS user_count FROM T WHERE DATEDIFF('2019-06-30', login_date) <= 90 GROUP BY 1;