(`lending_id` int, `installment_n` int, `status` varchar(15));

INSERT INTO Table1

(`lending_id`, `installment_n`, `status`)

(71737, 1, 'PAID'),

(71737, 2, 'PAID'),

(71737, 3, 'PAID'),

(71737, 4, 'PAID'),

(71737, 5, 'PAID'),

(71737, 6, 'WAITING_PAYMENT'),

(71737, 7, 'WAITING_PAYMENT'),

(71737, 8, 'WAITING_PAYMENT'),

(71737, 9, 'WAITING_PAYMENT'),

(71737, 10, 'WAITING_PAYMENT'),

(71737, 11, 'WAITING_PAYMENT'),

(71737, 12, 'WAITING_PAYMENT'),

(71737, 13, 'WAITING_PAYMENT'),

(71737, 14, 'WAITING_PAYMENT'),

(71737, 15, 'WAITING_PAYMENT'),

(71737, 16, 'WAITING_PAYMENT'),

(71737, 17, 'WAITING_PAYMENT'),

(71737, 18, 'WAITING_PAYMENT'),

(71737, 19, 'WAITING_PAYMENT'),

(71737, 20, 'WAITING_PAYMENT'),

(71737, 21, 'WAITING_PAYMENT'),

(354226, 1, 'PAID'),

(354226, 2, 'PAID'),

(354226, 3, 'WAITING_PAYMENT'),

(354226, 4, 'WAITING_PAYMENT'),

(354226, 5, 'WAITING_PAYMENT'),

(354226, 6, 'WAITING_PAYMENT'),

(354226, 7, 'PAID'),

(354226, 8, 'WAITING_PAYMENT'),

(354226, 9, 'WAITING_PAYMENT'),

(354226, 10, 'WAITING_PAYMENT'),

(354226, 11, 'WAITING_PAYMENT'),

lending_id, status, start_at_inst, end_at_inst, inst_count

from (

select

lending_id

, status

, grpby

, min(installment_n) start_at_inst

, max(installment_n) end_at_inst

, (max(installment_n) + 1) - min(installment_n) inst_count

, row_number() over(partition by lending_id order by (max(installment_n) + 1) - min(installment_n) DESC) rn

from (

select

t.*

, installment_n - row_number() over(partition by lending_id, status order by installment_n) grpby

from Table1 t

) d1

group by

lending_id, status, grpby

) d2

where rn = 1

;