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

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:

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

CodePudding user response：

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';

CodePudding user response：

Since you are looking for a count of active users nearest to the supplied date, the following would work.

select count(distinct userid) 
  from table
where modified <= '01.07.2020'
  and status='Active'