我为设备监控报告做了类似的工作。我从来没有想出一个解决方案，不涉及建立一个日历和交叉连接到一个独特的设备列表( user 价值观）。
这个故意冗长的查询构建交叉连接，获取每个连接的活动计数 user 以及 ddate ，执行运行 sum() 超过七天，然后统计给定 ddate 在那之后的七天里，有538个或更多的活动 ddate .

with drange as (
  select min(ts) as start_ts, max(ts) as end_ts
    from activeusers
), alldates as (
  select (start_ts + make_interval(days := x))::date as ddate
    from drange
   cross join generate_series(0, date_part('day', end_ts - start_ts)::int) as gs(x)
), user_dates as (
  select ddate, "user"
    from alldates
   cross join (select distinct "user" from activeusers) u
), user_date_counts as (
  select u.ddate, u."user",
         sum(case when a.user is null then 0 else 1 end) as actives
    from user_dates u
    left join activeusers a
           on a."user" = u."user"
          and a.ts::date = u.ddate
   group by u.ddate, u."user"
), running_window as (
  select ddate, "user",
         sum(actives) over (partition by user
                                order by ddate
                         rows between 6 preceding
                                  and current row) seven_days
    from user_date_counts
), flag_active as (
  select ddate, "user",
         seven_days >= 538 as is_active
    from running_window
)
select ddate, count(*) as active_users
  from flag_active
 where is_active
 group by ddate
;

因为您需要每天的活动用户，但要按周确定，所以我认为您可以使用交叉应用程序来复制每天的计数。查询的from部分将给出天数和用户，交叉应用将限制为活动用户。你可以在最后指定你想要的用户或日期。

SELECT users.UserName, users.LogDate
FROM (
    SELECT UserName, CAST(ts AS DATE) AS LogDate
    FROM activeusers
    GROUP BY CAST(ts AS DATE)
    ) AS users
CROSS APPLY (
    SELECT UserName, COUNT(1)
    FROM activeusers AS a
    WHERE a.UserName = users.UserName AND CAST(ts AS DATE) BETWEEN DATEADD(WEEK, -1, LogDate) AND LogDate
    GROUP BY UserName
    HAVING COUNT(1) >= 538
    ) AS activeUsers
WHERE users.LogDate > '2020-01-01' AND users.UserName = 'user1'

这是sql server，您可能需要对postgresql进行修订。交叉应用可以转换为左连接横向（…）为真。

由此产生的特殊困难是，如果用户在前6天内连接充分，他们可能有资格在没有任何连接的情况下工作。
这使得使用窗口函数变得更加困难。聚集在一个 LATERAL 子查询是显而易见的选择：

WITH daily AS (  -- ① granulate daily
   SELECT ts::date AS the_day
        , "user"
        , count(*)::int AS daily_cons
   FROM   activeusers
   GROUP  BY 1, 2
  )
SELECT d.the_day, count("user") AS active_users
FROM  ( --  ② time frame
   SELECT generate_series (timestamp '2020-07-01'
                         , LOCALTIMESTAMP
                         , interval '1 day')::date
   ) d(the_day)
LEFT   JOIN LATERAL (
   SELECT "user"
   FROM   daily d
   WHERE  d.the_day >= d.the_day - 6
   AND    d.the_day <= d.the_day
   GROUP  BY "user"
   HAVING sum(daily_cons) >= 538  -- ③
   ) sum7 ON true
ORDER  BY d.the_day;

① cte daily 是可选的，但是从每日聚合开始应该对性能有很大帮助。
② 你得确定一下时间范围。我选择了当年。替换为您的选择。要使用表中的总范围，请改用：

SELECT generate_series (min(the_day)::timestamp
                      , max(the_day)::timestamp
                      , interval '1 day')::date AS the_day
FROM   daily

请考虑以下基本问题：
在postgresql中生成两个日期之间的时间序列
这也克服了上述“特殊困难”。
③ 天气状况 HAVING 子句将消除过去7天（包括“今天”）中连接不足的所有行。
相关：
按月累计值之和，填写缺失月份
在rails+postgres中按任意时间间隔统计记录的最佳方法
每周记录总数
旁白：
您不会真正使用保留字“user”作为标识符。