LeetCode 1107. New Users Daily Count SQL Solution

Problem

LeetCode SQL Problem

1107. New Users Daily Count

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

Solution

MySQL

New Users Daily Count

SET @today = '2019-06-30';
SET @three_months_ago = timestampadd(day, - 90, @today);

-- For every date within at most 90 days from today:
-- Report the number of users that logged in for the first time on that date.
SELECT T.activity_date AS login_date
	,count(DISTINCT T.user_id) AS user_count
FROM Traffic AS T
WHERE T.activity = 'login'
	AND T.activity_date >= @three_months_ago -- Within at most 90 days from today
	AND T.user_id NOT IN (
		-- Get distinct user_id values that have logged in more than 3 months ago
		SELECT DISTINCT user_id
		FROM Traffic
		WHERE activity_date < @three_months_ago
			AND activity = 'login'
		)
GROUP BY T.activity_date
ORDER By T.activity_date

TSQL

New Users Daily Count

DECLARE @today DATE = '2019-06-30';
DECLARE @three_months_ago DATE = dateadd(day, - 90, @today);

-- For every date within at most 90 days from today:
-- Report the number of users that logged in for the first time on that date.
SELECT T.activity_date AS login_date
	,count(DISTINCT T.user_id) AS user_count
FROM Traffic AS T
WHERE T.activity = 'login'
	AND T.activity_date >= @three_months_ago -- Within at most 90 days from today
	AND T.user_id NOT IN (
		-- Get distinct user_id values that have logged in more than 3 months ago
		SELECT DISTINCT user_id
		FROM Traffic
		WHERE activity_date < @three_months_ago
			AND activity = 'login'
		)
GROUP BY T.activity_date
ORDER By T.activity_date

Query Output

login_date	user_count
2019-05-01	1
2019-06-21	2