10000 Initial Commit · priyesh0453/SQL-LeetCode@b733cc3 · GitHub
[go: up one dir, main page]
Skip to content

Commit b733cc3

Browse files
priyesh0453priyesh0453
committed
Initial Commit
1 parent 7b8dd50 commit b733cc3

File tree

1 file changed

+71
-0
lines changed

1 file changed

+71
-0
lines changed

‎Easy/1141. User Activity for the Past 30 Days I.sql

Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,71 @@
1+
/*
2+
Table: Activity
3+
4+
+---------------+---------+
5+
| Column Name | Type |
6+
+---------------+---------+
7+
| user_id | int |
8+
| session_id | int |
9+
| activity_date | date |
10+
| activity_type | enum |
11+
+---------------+---------+
12+
There is no primary key for this table, it may have duplicate rows.
13+
The activity_type column is an ENUM of type ('open_session', 'end_session', 'scroll_down', 'send_message').
14+
The table shows the user activities for a social media website.
15+
Note that each session belongs to exactly one user.
16+
17+
18+
Required Query:
19+
20+
1) Find the daily active user count for a period of 30 days ending 2019-07-27 inclusively. A user was active on someday if they made at least one activity on that day.
21+
22+
23+
SQL Schema:
24+
25+
Create table If Not Exists Activity (user_id int, session_id int, activity_date date, activity_type ENUM('open_session', 'end_session', 'scroll_down', 'send_message'))
26+
Truncate table Activity
27+
insert into Activity (user_id, session_id, activity_date, activity_type) values ('1', '1', '2019-07-20', 'open_session')
28+
insert into Activity (user_id, session_id, activity_date, activity_type) values ('1', '1', '2019-07-20', 'scroll_down')
29+
insert into Activity (user_id, session_id, activity_date, activity_type) values ('1', '1', '2019-07-20', 'end_session')
30+
insert into Activity (user_id, session_id, activity_date, activity_type) values ('2', '4', '2019-07-20', 'open_session')
31+
insert into Activity (user_id, session_id, activity_date, activity_type) values ('2', '4', '2019-07-21', 'send_message')
32+
insert into Activity (user_id, session_id, activity_date, activity_type) values ('2', '4', '2019-07-21', 'end_session')
33+
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '2', '2019-07-21', 'open_session')
34+
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '2', '2019-07-21', 'send_message')
35+
insert into Activity (user_id, session_id, activity_date, activity_type) values ('3', '2', '2019-07-21', 'end_session')
36+
insert into Activity (user_id, session_id, activity_date, activity_type) values ('4', '3', '2019-06-25', 'open_session')
37+
insert into Activity (user_id, session_id, activity_date, activity_type) values ('4', '3', '2019-06-25', 'end_session')
38+
39+
Example:
40+
41+
Input:
42+
Activity table:
43+
+---------+------------+---------------+---------------+
44+
| user_id | session_id | activity_date | activity_type |
45+
+---------+------------+---------------+---------------+
46+
| 1 | 1 | 2019-07-20 | open_session |
47+
| 1 | 1 | 2019-07-20 | scroll_down |
48+
| 1 | 1 | 2019-07-20 | end_session |
49+
| 2 | 4 | 2019-07-20 | open_session |
50+
| 2 | 4 | 2019-07-21 | send_message |
51+
| 2 | 4 | 2019-07-21 | end_session |
52+
| 3 | 2 | 2019-07-21 | open_session |
53+
| 3 | 2 | 2019-07-21 | send_message |
54+
| 3 | 2 | 2019-07-21 | end_session |
55+
| 4 | 3 | 2019-06-25 | open_session |
56+
| 4 | 3 | 2019-06-25 | end_session |
57+
+---------+------------+---------------+---------------+
58+
Output:
59+
+------------+--------------+
60+
| day | active_users |
61+
+------------+--------------+
62+
| 2019-07-20 | 2 |
63+
| 2019-07-21 | 2 |
64+
+------------+--------------+
65+
*/
66+
67+
-- Solution:
68+
SELECT activity_date AS day, COUNT(DISTINCT user_id) AS active_users
69+
FROM Activity
70+
WHERE activity_date BETWEEN DATE_ADD('2019-07-27', INTERVAL -29 DAY) AND '2019-07-27'
71+
GROUP BY activity_date

0 commit comments

Comments
 (0)
0