SQL Find max no of consecutive months over a period of last 12 Months

CTEs can break this down a little easier. In the code below, the payment_streak CTE is the key bit; the start_of_streak field is first marking rows that count as the start of a streak, and then taking the maximum over all previous rows (to find the start of this streak).

The last SELECT is only comparing these two dates, computing how many months are between them (excluding June/July), and then finding the best streak per customer.

WITH payments_in_context AS (
  SELECT customer_number,
    date,
    lag(date) OVER (PARTITION BY customer_number ORDER BY date) AS prev_date
  FROM Table1
  WHERE EXTRACT(month FROM date) NOT IN (6,7)
),
payment_streak AS (
  SELECT 
    customer_number,
    date,
    max(
    CASE WHEN (prev_date IS NULL)
           OR (EXTRACT(month FROM date) <> 8
                 AND (date - prev_date >= 62 
                    OR MOD(EXTRACT(month FROM date) - EXTRACT(month FROM prev_date),12) > 1))
           OR (EXTRACT(month FROM date) = 8
                 AND (date - prev_date >= 123
                    OR EXTRACT(month FROM prev_date) NOT IN (5,8)))
         THEN date END
    ) OVER (PARTITION BY customer_number ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    as start_of_streak
    FROM payments_in_context
)
SELECT customer_number,
  max( 1 +
    10*(EXTRACT(year FROM date) - EXTRACT(year FROM start_of_streak))
      + (EXTRACT(month FROM date) - EXTRACT(month FROM start_of_streak))
      - CASE WHEN (EXTRACT(month FROM date) > 7 AND EXTRACT(month FROM start_of_streak) < 6)
             THEN 2
             ELSE 0 END)
     ) AS max_consecutive_months
FROM payment_streak
GROUP BY 1;