Approach: compute each user's average events per day over the past 7 days (including days with zero events by dividing by 7), compute the median of daily event counts across all users and days in the same 7-day window, then return users whose avg > 3 * median. Assumption: timestamps are in UTC (or normalized) and the 7-day window is the last 7 full days including today (use CURRENT_TIMESTAMP). Adjust DATE(occurred_at AT TIME ZONE ...) if needed for another timezone.SQL (standard SQL with window function percentile_cont for median):sql
WITH last7 AS (
SELECT *
FROM events
WHERE occurred_at >= CURRENT_TIMESTAMP - INTERVAL '7' DAY
AND occurred_at <= CURRENT_TIMESTAMP
),
user_day_counts AS (
-- count events per user per day
SELECT
user_id,
DATE(occurred_at) AS day,
COUNT(*) AS events_on_day
FROM last7
GROUP BY user_id, DATE(occurred_at)
),
all_user_days AS (
-- include days with zero by generating 7-day grid per user if needed
SELECT u.user_id, d.day
FROM (SELECT DISTINCT user_id FROM last7) u
CROSS JOIN (
SELECT DATE(CURRENT_DATE - s.seq) AS day
FROM (VALUES (0),(1),(2),(3),(4),(5),(6)) AS s(seq)
) d
),
user_day_full AS (
-- left join to ensure zeros for missing days
SELECT a.user_id, a.day, COALESCE(ud.events_on_day, 0) AS events_on_day
FROM all_user_days a
LEFT JOIN user_day_counts ud
ON a.user_id = ud.user_id AND a.day = ud.day
),
user_avg AS (
-- average per day over 7 days (sum/7)
SELECT
user_id,
SUM(events_on_day)::DECIMAL / 7.0 AS avg_events_per_day
FROM user_day_full
GROUP BY user_id
),
median_all AS (
-- median of daily events across all users and days
SELECT
percentile_cont(0.5) WITHIN GROUP (ORDER BY events_on_day) AS median_daily_events
FROM user_day_full
)
SELECT ua.user_id, ua.avg_events_per_day
FROM user_avg ua
CROSS JOIN median_all m
WHERE ua.avg_events_per_day > 3 * m.median_daily_events
ORDER BY ua.avg_events_per_day DESC;
