MediumNo. 1107LeetCode Premium
New Users Daily Count — LeetCode 1107 Python Solution
- Topics
- Database
- Reading time
- 3 min
- Source
- leetcode.com
Table schema
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').Example
+---------------+---------+
| 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').Python solution
import duckdb
import pandas as pd
def solution(traffic: pd.DataFrame) -> pd.DataFrame:
con = duckdb.connect()
con.register("Traffic", traffic)
return con.execute("""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;""").df()Complexity
- Time
- O(n log n)(typical)
- Space · auxiliary
- O(n)
Related problems
| No. | Problem | Difficulty |
|---|---|---|
| 0175 | Combine Two Tables | Easy |
| 0176 | Second Highest Salary | Medium |
| 0177 | Nth Highest Salary | Medium |
| 0178 | Rank Scores | Medium |
| 0180 | Consecutive Numbers | Medium |
| 0181 | Employees Earning More Than Their Managers | Easy |
Frequently asked questions
- How hard is LeetCode 1107. New Users Daily Count?
LeetCode 1107. New Users Daily Count is rated Medium on LeetCode.
- What topics does LeetCode 1107. New Users Daily Count cover?
LeetCode 1107. New Users Daily Count is tagged Database on LeetCode.
- Is LeetCode 1107. New Users Daily Count a premium problem?
Yes. LeetCode 1107. New Users Daily Count is a LeetCode Premium problem, so the full statement and test cases require a paid LeetCode subscription.