Task: activity on 4 consecutive days

I remove duplicate days, then check whether the third previous active day is exactly three days earlier.

-- activity(user_id, active_day date)
WITH days AS (
  SELECT DISTINCT user_id, active_day
  FROM activity WHERE active_day IS NOT NULL
), checked AS (
  SELECT user_id, active_day,
    LAG(active_day, 3) OVER (
      PARTITION BY user_id ORDER BY active_day
    ) AS first_day
  FROM days
)
SELECT DISTINCT user_id
FROM checked
WHERE active_day - first_day = 3;

Why it works: after deduplication, four ordered, distinct dates that fit within three days must be consecutive. A longer streak also meets the condition.

Pitfall: for timestamps I first settle the time zone and the business date. I don't use LAG(..., 4), because four days are three gaps.