MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

WITH yourTable AS (

SELECT 1 AS Id, 'a@b.com' AS Email UNION ALL

SELECT 2, 'c@d.com' UNION ALL

SELECT 3, 'a@b.com' UNION ALL

SELECT 4, 'a@b.com' UNION ALL

SELECT 5, 'a@b.com'

cte AS (

SELECT *, ROW_NUMBER() OVER (ORDER BY Id) rn1,

ROW_NUMBER() OVER (PARTITION BY Email ORDER BY Id) rn2

FROM yourTable

)

SELECT Id, Email, COUNT(*) OVER (PARTITION BY Email, rn1-rn2 ORDER BY Id) AS cnt

FROM cte

ORDER BY Id;