Selecting a count of distinct logins per day

Question

I have an activity_log table that I need to derive a basic output of how many distinct users logged in that day, and sum up them up day by day. The table logs all actions of a user and will have several entries per day for each user. Here is the table -

This is what I have so far -

select DISTINCT CAST(test.activitytime AS date) Date, username from [dbo].[activity_log] as test
where test.activitytime between '2024-03-01' and '2024-03-08'
order by Date

Here is the output for that -

I need it to display every day once, and sum up the total of distinct users per day (does not matter if they logged in yesterday as well) just total distinct users a day. I am lost on where to go from here

ie

2024-03-01 24

2024-03-02 39

2024-03-03 26

Answer 1

A distinct count should achieve the result you're looking for.

SELECT CONVERT(date, activitytime) AS [Date],
COUNT(DISTINCT [username]) AS [UserCount]
FROM [dbo].[activity_log]
WHERE CONVERT(date, activitytime) BETWEEN '2024-03-01' and '2024-03-08'
GROUP BY CONVERT(date, activitytime)
ORDER BY 1;

Answer 2

select activitytime,sum(case when rk = 1 then 1 else 0 end) unique_users_count
from 
(
select activitytime,[user_id],rank() over(partition by [user_id] order by activitytime) rk 
from @Activity
) k
group by activitytime