Oracle SQL: check amount of active users on given date (check closest date of grouped field)

Have a table given, holding the status history of a user:

ID	USERID	MODIFIED	STATUS
1	1	01.01.2020	inactive
2	1	01.07.2020	active
3	2	04.08.2020	active
4	2	04.06.2020	active
5	2	01.08.2020	inactive
6	2	01.10.2020	active
7	3	01.09.2020	inactive

I want to provide a date, i.e. 01.07.2020, and understand how many UserIds were active on that day.

I therefor need to check the modified date which is closest but not above 01.07.2020, grouped by the userid.

Desired result for 01.07.2020:

ID	USERID	MODIFIED	STATUS
2	1	01.07.2020	active
4	2	04.06.2020	active

From there I could just sum the status, and see I had two users active on the checked date of 01.07.2020.

Current approach for first step:

select max(id), userid, max(modified)
        keep (dense_rank first order by modified) as id
from MY_TABLE
where modified <= '01.07.2020'
group by userid;

it does not yet provide fully correct results

the final step would then be a simple sum I assume, something like:

Select sum(case when status = 'active' then 1 else 0 end) as "active_users"
from MY_TABLE t1
inner join (
        select max(id)
            keep (dense_rank first order by modified) as id
        from MY_TABLE
        where modified <= '01.07.2020'
        group by userid
) t2 on t1.id = t2.id

Answer

You can use row_number() to get the last status as of that date:

select count(*)
from (select t.*,
             row_number() over (partition by userid order by modified desc) as seqnum
      from my_table t
      where t.modified <= date '2020-07-01'
     ) t
where seqnum = 1 and status = 'Active';

Another option is a correlated subquery:

select count(*)
from my_table t
where t.modified = (select max(t2.modified)
                    from my_table t2
                    where t2.userid = t.userid and
                          t2.modified <= date '2020-07-01'
                   ) and
      t.status = 'Active';

Or, you can use two levels of aggregation:

select count(*)
from (select userid,
             max(status) keep (dense_rank first order by modified desc) as status
      from my_table t
      where t.modified <= date '2020-07-01'
      group by userid
     ) t
where status = 'Active';