-- Island detection: consecutive active days per employee
SELECT
  emp_id,
  MIN(work_date) AS island_start,
  MAX(work_date) AS island_end,
  COUNT(*) AS consecutive_days
FROM (
  SELECT
    emp_id,
    work_date,
    work_date - MAKE_INTERVAL(days => ROW_NUMBER() OVER (
      PARTITION BY emp_id ORDER BY work_date
    )::int) AS island_group
  FROM attendance
) island_data
GROUP BY emp_id, island_group
ORDER BY emp_id, island_start;