WITH login_days AS (
  SELECT DISTINCT user_id, login_date::DATE AS d
  FROM user_logins
),
numbered AS (
  SELECT
    user_id, d,
    d - MAKE_INTERVAL(days => ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY d)::INT) AS grp
  FROM login_days
),
streaks AS (
  SELECT user_id, grp, COUNT(*) AS streak_len,
    MIN(d) AS start_date, MAX(d) AS end_date
  FROM numbered
  GROUP BY user_id, grp
)
SELECT
  u.user_id,
  MAX(s.streak_len) AS longest_streak,
  SUM(s.streak_len) AS total_active_days
FROM streaks s
JOIN users u ON s.user_id = u.user_id
GROUP BY u.user_id
ORDER BY longest_streak DESC;