Como uma dica extra divertida enquanto Isaac analisa isso, você também pode usar o parâmetro user_id para obter uma caixa de parâmetro de preenchimento automático elegante. Exemplo:
-- [params]
-- user_id :user
-- int :min_days = 10
WITH consecutive_visits AS (
SELECT user_id,
visited_at,
-- O valor de s será o mesmo para cada grupo de dias consecutivos
visited_at - (DENSE_RANK() OVER (PARTITION BY user_id ORDER BY visited_at))::int s
FROM user_visits
WHERE user_id = :user
)
SELECT
MIN(visited_at) period_start,
COUNT(*) AS consecutive_days,
MAX(visited_at) period_end
FROM consecutive_visits
GROUP BY user_id, s
HAVING COUNT(*) >= :min_days
ORDER BY period_start DESC