您似乎只需要条件聚合：

select event_dt,
       sum(case when status = 'Registered' then 1 else 0 end) as registered,
       sum(case when status = 'active_acct' then 1 else 0 end) as active_acct,
       sum(case when status = 'suspended' then 1 else 0 end) as suspended,
       sum(case when status = 'reactive' then 1 else 0 end) as reactive
from table.A 
group by event_dt
order by event_dt;

编辑：
这是个棘手的问题。我提出的解决方案是对日期和用户进行叉积，然后计算每个日期的最新状态。
所以：

select a.event_dt,
       sum(case when aa.status = 'Registered' then 1 else 0 end) as registered,
       sum(case when aa.status = 'active_acct' then 1 else 0 end) as active_acct,
       sum(case when aa.status = 'suspended' then 1 else 0 end) as suspended,
       sum(case when aa.status = 'reactive' then 1 else 0 end) as reactive
from (select d.event_dt, ac.account, a.status,
             max(case when a.status is not null then a.timestamp end) over (partition by ac.account order by d.event_dt) as last_status_timestamp
      from (select distinct event_dt from table.A) d cross join
           (select distinct account from table.A) ac left join
           (select a.*,
                   row_number() over (partition by account, event_dt order by timestamp desc) as seqnum
            from table.A a
           ) a
           on a.event_dt = d.event_dt and
              a.account = ac.account and
              a.seqnum = 1  -- get the last one on the date
     ) a left join
     table.A aa
     on aa.timestamp = a.last_status_timestamp and
        aa.account = a.account
group by d.event_dt
order by d.event_dt;

这样做的目的是创建一个派生表，其中包含所有帐户和日期的行。它在某些天具有状态，但不是所有天。
的累计最大值 last_status_timestamp 计算具有有效状态的最新时间戳。然后将其连接回表以获取该日期的状态。瞧！这是用于条件聚合的状态。
累积max和join是一个解决方法，因为hive（还？）不支持 ignore nulls 中的选项 lag() .