Как в SQL посчитать количество пользователей записей, дата которых хотя бы раз была в каждую неделю?

Question

[bad_shape_guy]

Добрый день, подскажите пожалуйста, имеется таблица из двух колонок LOGIN, LOGON_DATETIME
типа:

user10 ; 2019-10-10 00:00:00
user1 ; 2020-10-15 00:00:00
user3 ; 2020-10-16 00:00:00
user1 ; 2021-10-17 00:00:00

нужно посчитать количество уникальных логинов которые каждую неделю хотя бы раз были в таблице (судя по полю даты) в 2020 году

Т.е. нужно узнать сколько пользователей в 2020 году обязательно каждую неделю хотя раз логинились

Comments

[Akina]

Что в данном случае "неделя"? если календарная - нужно ли учитывать неполные недели в начале и конце года?

А вообще - для каждой даты получаете номер недели, используя WEEK() либо WEEKOFYEAR(), потом группируете по логину и считаете COUNT(DISTINCT). Кто соответствует критерию, у того посчитанное количество будет равно (смотрим в HAVING) количеству недель в году.

Answer 1

[Дмитрий]

SELECT count(distinct login), week(logon_datetime) as _week FROM users GROUP BY _week

если надо за конкретный год, то

SELECT count(distinct login), week(logon_datetime) as _week FROM users WHERE year(logon_datetime) = 2020 GROUP BY _week

Comments

[bad_shape_guy]

Спасибо, но получается ваш запрос показывает просто количество логинов каждую неделю, тут задача чтобы на протяжении всего года без разрывов стабильно каждую неделю как минимум один раз логинился
Т.е. сколько всего пользователей постоянно в течении года каждую неделю минимум 1 раз логинятся

Answer 2

[mayton2019]

Можешь взять за основу этот шаблон. И дописать его.

with temp as (
select 
  user,
  log_time,
  LAG(log_time) OVER (PARTITION BY user ORDER BY log_time) as prev_log_time
from logins)
select user, date_diff(log_time , prev_log_time) as days_diff from temp

В задании неясно что делать с пользователями
которые за всю историю заходили только один раз. Это маржинальный кейс.

И напиши какие ожидается получить отчеты в ответ на твою табличку.

Answer 3

[Boris Köln]

Сначала выбрать юзеров и неделю. Потом из предыдущей таблицы выбрать юзеров, у которых количество недель равно количеству недель в исходной таблице

SELECT user_id
FROM (
    SELECT user_id,
           YEARWEEK(datetime, 1) AS year_week,
           COUNT(DISTINCT YEARWEEK(datetime, 1)) AS weeks_count
    FROM your_table
    GROUP BY user_id, YEARWEEK(datetime, 1)
) AS user_weeks
GROUP BY user_id
HAVING COUNT(DISTINCT year_week) = (
    SELECT COUNT(DISTINCT YEARWEEK(datetime, 1)) 
    FROM your_table
);