Count Unique Values Throughout One Day For Nonconsecutive Student ID's

Question

I am trying to figure out how to count all instances where a student is online without counting duplicate instances.

For example, in the screenshot below, I want to see a column counting only instances where a student is logged in. So, if Student A is logged in at 5 AM, count = 1. Student B logged in at 7, Count = 2. At some point student A logged off and logged back on at 8 am, the count should be 2, not 3.

Thank you!

Student	Time.	Desired Column (Count)
A	5 AM	1
B	7 AM	2
A	8 AM	2
C	9 AM	3
D	10 AM	4
E	11 AM	5
D	12 PM	5

I am mainly trying to track the activity and only count when someone is logged in. If those students appear multiple times, we can assume they logged off at some point and logged back in. It's basically a unique running count. Not sure how to write this in SQL. I hope this makes sense.

Answer 1

One option, use the exists operator with a correlated subquery to check if the student has logged in before:

SELECT Student, Time_,
  SUM(flag) OVER (ORDER BY  Time_) AS expected_count
FROM
(
  SELECT *,
    CASE
     WHEN EXISTS(SELECT 1 FROM table_name D WHERE D.Student = T.Student AND D.Time_<T.Time_)
     THEN 0 ELSE 1
    END AS flag
  FROM table_name T
) D
ORDER BY Time_

See demo.