MySQL 8.0

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

create table table1 (
  user_id int,
  start_date datetime,
  end_date datetime
);

insert into table1 values
(11122, '2017-04-20', '2018-02-17'),
(11123, '2018-01-20', '2018-02-17');

Records: 2  Duplicates: 0  Warnings: 0


WITH RECURSIVE cte AS
(
  SELECT user_id, DATE_FORMAT(start_date, '%Y-%m-03') as third_day,
  DATE_FORMAT(start_date, '%Y-%m-24') as twenty_fourth_day,
  start_date, end_date
  FROM table1
  UNION ALL
  SELECT user_id, 
  DATE_FORMAT(third_day + INTERVAL 1 MONTH, '%Y-%m-03') as third_day,
  DATE_FORMAT(twenty_fourth_day + INTERVAL 1 MONTH, '%Y-%m-24') as twenty_fourth_day,
  start_date, 
  end_date
  FROM cte
  WHERE third_day + INTERVAL 1 MONTH <= end_date
)
SELECT * FROM cte WHERE user_id = 11122


user_id
third_day
twenty_fourth_day
start_date
end_date


11122
2017-04-03
2017-04-24
2017-04-20 00:00:00
2018-02-17 00:00:00

11122
2017-05-03
2017-05-24
2017-04-20 00:00:00
2018-02-17 00:00:00

11122
2017-06-03
2017-06-24
2017-04-20 00:00:00
2018-02-17 00:00:00

11122
2017-07-03
2017-07-24
2017-04-20 00:00:00
2018-02-17 00:00:00

11122
2017-08-03
2017-08-24
2017-04-20 00:00:00
2018-02-17 00:00:00

11122
2017-09-03
2017-09-24
2017-04-20 00:00:00
2018-02-17 00:00:00

11122
2017-10-03
2017-10-24
2017-04-20 00:00:00
2018-02-17 00:00:00

11122
2017-11-03
2017-11-24
2017-04-20 00:00:00
2018-02-17 00:00:00

11122
2017-12-03
2017-12-24
2017-04-20 00:00:00
2018-02-17 00:00:00

11122
2018-01-03
2018-01-24
2017-04-20 00:00:00
2018-02-17 00:00:00

11122
2018-02-03
2018-02-24
2017-04-20 00:00:00
2018-02-17 00:00:00


WITH RECURSIVE cte AS
(
  SELECT user_id, DATE_FORMAT(start_date, '%Y-%m-03') as third_day,
   DATE_FORMAT(start_date, '%Y-%m-24') as twenty_fourth_day,
   start_date, end_date
   FROM table1
  UNION ALL
  SELECT user_id, 
   DATE_FORMAT(third_day + INTERVAL 1 MONTH, '%Y-%m-03') as third_day,
   DATE_FORMAT(twenty_fourth_day + INTERVAL 1 MONTH, '%Y-%m-24') as twenty_fourth_day,
   start_date, end_date
  FROM cte
  WHERE third_day + INTERVAL 1 MONTH <= end_date
)
SELECT user_id,
 SUM(CASE WHEN third_day BETWEEN start_date AND end_date THEN 1 ELSE 0 END) AS number_of_third_day,
 SUM(CASE WHEN twenty_fourth_day BETWEEN start_date AND end_date THEN 1 ELSE 0 END) AS number_of_twenty_fourth_day
FROM cte
GROUP BY user_id;


user_id
number_of_third_day
number_of_twenty_fourth_day


11122
10
10

11123
1
1

user_id	third_day	twenty_fourth_day	start_date	end_date
11122	2017-04-03	2017-04-24	2017-04-20 00:00:00	2018-02-17 00:00:00
11122	2017-05-03	2017-05-24	2017-04-20 00:00:00	2018-02-17 00:00:00
11122	2017-06-03	2017-06-24	2017-04-20 00:00:00	2018-02-17 00:00:00
11122	2017-07-03	2017-07-24	2017-04-20 00:00:00	2018-02-17 00:00:00
11122	2017-08-03	2017-08-24	2017-04-20 00:00:00	2018-02-17 00:00:00
11122	2017-09-03	2017-09-24	2017-04-20 00:00:00	2018-02-17 00:00:00
11122	2017-10-03	2017-10-24	2017-04-20 00:00:00	2018-02-17 00:00:00
11122	2017-11-03	2017-11-24	2017-04-20 00:00:00	2018-02-17 00:00:00
11122	2017-12-03	2017-12-24	2017-04-20 00:00:00	2018-02-17 00:00:00
11122	2018-01-03	2018-01-24	2017-04-20 00:00:00	2018-02-17 00:00:00
11122	2018-02-03	2018-02-24	2017-04-20 00:00:00	2018-02-17 00:00:00