La dificultad especial resultante aquí es que los usuarios pueden calificar para días en los que no tienen ninguna conexión, si estuvieron suficientemente conectados durante los 6 días anteriores.

Eso hace que sea más difícil usar una función de ventana. Agregar en una subconsulta LATERAL es la alternativa obvia:

 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;

① El CTE daily es opcional, pero comenzar con agregados diarios debería ayudar mucho al rendimiento.

② Tendrás que definir el marco de tiempo de alguna manera . Elegí el año en curso. Reemplace con su elección. Para trabajar con el rango total presente en su tabla, use en su lugar:

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

Considere los conceptos básicos aquí:

• Generando series de tiempo entre dos fechas en PostgreSQL

Esto también supera la "dificultad especial" mencionada anteriormente.

③ La condición en la cláusula HAVING elimina todas las filas con conexiones insuficientes durante los últimos 7 días (incluido "hoy").

Relacionada:

• Suma acumulada de valores por mes, completando los meses que faltan
• La mejor manera de contar registros por intervalos de tiempo arbitrarios en Rails+Postgres
• Número total de registros por semana

Aparte:
Realmente no usaría la palabra reservada "usuario" como identificador.

Debido a que desea el usuario activo para todos los días, pero está determinando por semana, creo que podría usar una APLICACIÓN CRUZADA para duplicar el conteo de todos los días. La parte DESDE de la consulta le dará los días y los usuarios, la APLICACIÓN CRUZADA se limitará a los usuarios activos. Puedes especificar en el DONDE final que usuarios o fechas quieres.

 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'

Este es SQL Server, es posible que deba hacer revisiones para PostgreSQL. CROSS APPLY puede traducirse como LEFT JOIN LATERAL (...) ON true.

He hecho algo similar a esto para los informes de monitoreo de dispositivos. Nunca pude encontrar una solución que no implique crear un calendario y unirlo a una lista distinta de dispositivos (valores de user en su caso).

Esta consulta deliberadamente detallada crea la unión cruzada, obtiene recuentos activos por user y ddate , realiza la ejecución sum() durante siete días y luego cuenta la cantidad de usuarios en una fecha dada que tenía ddate o más activos en los siete días que terminan en esa 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 ;