Пользователи за последний час, не заходившие сутки до этого

Эту задачу дают на собеседовании, чтобы проверить не столько SQL, сколько умение вслух проговорить границы временного окна — на них ошибаются чаще, чем на синтаксисе.

Задача

Есть лог входов в приложение user_events(userid, dttm) — одна строка это один вход пользователя в момент dttm.

Посчитай, сколько пользователей заходили за последний 1 час и при этом не заходили в предыдущие 24 часа — то есть в интервал от «25 часов назад» до «1 час назад».

Схема данных

CREATE TABLE user_events (userid int, dttm timestamp);

Данные заданы относительно now(), поэтому задача решается «здесь и сейчас» — состав ответа зависит от текущего момента и меняется по мере того, как события в песочнице «стареют».

Решение

SELECT COUNT(DISTINCT e.userid) AS users
FROM user_events e
WHERE e.dttm > now() - INTERVAL '1 hour'
  AND NOT EXISTS (
    SELECT 1
    FROM user_events p
    WHERE p.userid = e.userid
      AND p.dttm > now() - INTERVAL '25 hour'
      AND p.dttm <= now() - INTERVAL '1 hour'
  );

Разбор

Сначала — то, ради чего задачу и придумали: границы окон. «Последний час» — это интервал (now − 1 час; now]. А «предыдущие 24 часа» отсчитываются не от now, а от точки «час назад» — то есть (now − 25 часов; now − 1 час]. Если по невнимательности взять (now − 24 часа; now], окна наложатся и ответ поедет. Проговори это вслух — интервьюер именно этого и ждёт.

Дальше — анти-джойн. Внешний фильтр dttm > now() − 1 hour оставляет тех, кто заходил в последний час. NOT EXISTS выкидывает тех, у кого нашёлся вход в «предыдущие сутки». Почему NOT EXISTS, а не NOT IN: последний коварно ведёт себя, если в подзапросе попадётся NULL, — NOT EXISTS от этого свободен.

И COUNT(DISTINCT userid), а не COUNT(*): если пользователь заходил за последний час дважды, по строкам он посчитается два раза, а нам нужны уникальные люди. Кстати, пользователь с совсем старым событием (больше 25 часов назад) в «предыдущие сутки» не попадает и остался бы в ответе. Про многошаговую логику в подзапросах — в вопросе подзапрос, CTE или временная таблица.

Ноль в ответе — тоже валидный результат. Данные в песочнице живые и заданы относительно момента заполнения, поэтому со временем они «стареют» относительно now(): события, которые изначально выглядели как «вход в последний час», через сутки-другие уже попадают и в текущий час, и в «предыдущие сутки» — и анти-джойн отсеивает всех. Само по себе users = 0 не значит, что запрос неверный.

Ожидаемый результат

users
---
0