MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE input_table (`text` VARCHAR(100), `year_month` CHAR(7));
INSERT INTO input_table VALUES
  ('text1','2023-03'),
  ('text1','2023-03'),
  ('text3','2023-03'),
  ('text5','2023-03'),
  ('text1','2023-02'),
  ('text2','2023-02'),
  ('text4','2023-02'),
  ('text3','2020-10'),
  ('text1','2019-01'),
  ('text4','2019-01'),
  ('text2','2020-01');

Records: 11  Duplicates: 0  Warnings: 0


/* Your query */
-- EXPLAIN
SELECT `year_month`, SUM(is_duplicate) AS duplicate_count
FROM (
    SELECT t1.`year_month`, t1.`text`, MAX(CASE WHEN t2.`year_month` IS NULL THEN 0 ELSE 1 END) AS is_duplicate
    FROM input_table t1
    LEFT JOIN input_table t2 ON t1.`text` = t2.`text` AND t1.`year_month` > t2.`year_month`
    GROUP BY t1.`year_month`, t1.`text`
) AS T
GROUP BY `year_month`


year_month
duplicate_count


2023-03
2

2023-02
3

2020-10
0

2019-01
0

2020-01
0


/* Your query with unnecessary subquery removed */
-- EXPLAIN
SELECT t1.year_month, COUNT(DISTINCT t2.text) AS duplicate_count
FROM input_table t1
LEFT JOIN input_table t2 ON t1.text = t2.text AND t1.year_month > t2.year_month
GROUP BY t1.year_month
ORDER BY t1.year_month DESC;


year_month
duplicate_count


2023-03
2

2023-02
3

2020-10
0

2020-01
0

2019-01
0


/* Kombajn's answer */
-- EXPLAIN
SELECT `year_month`, count(nullif(r, 1)) as duplicate_count
  FROM (SELECT `text`, `year_month`,
               rank() over (partition by `text` order by `year_month`) as r
          FROM input_table) x
 GROUP BY `year_month`
 ORDER BY `year_month` DESC;


year_month
duplicate_count


2023-03
3

2023-02
3

2020-10
0

2020-01
0

2019-01
0


SELECT `text`, `year_month`,
       RANK() OVER (PARTITION BY `text` ORDER BY `year_month`) AS r
FROM `input_table`
ORDER BY `year_month` DESC, `text` ASC;


text
year_month
r


text1
2023-03
3

text1
2023-03
3

text3
2023-03
2

text5
2023-03
1

text1
2023-02
2

text2
2023-02
2

text4
2023-02
2

text3
2020-10
1

text2
2020-01
1

text1
2019-01
1

text4
2019-01
1


/* Kombajn's answer - tweaked to handle duplicates within same month */
-- EXPLAIN
SELECT `year_month`, COUNT(DISTINCT IF(r > 1, `text`, NULL)) AS duplicate_count
FROM (
    SELECT `text`, `year_month`, RANK() OVER (PARTITION BY `text` ORDER BY `year_month`) AS r
    FROM input_table
) x
GROUP BY `year_month`
ORDER BY `year_month` DESC;


year_month
duplicate_count


2023-03
2

2023-02
3

2020-10
0

2020-01
0

2019-01
0
text	year_month	r
text1	2023-03	3
text1	2023-03	3
text3	2023-03	2
text5	2023-03	1
text1	2023-02	2
text2	2023-02	2
text4	2023-02	2
text3	2020-10	1
text2	2020-01	1
text1	2019-01	1
text4	2019-01	1