MySQL Query Getting Wrong Data for Count

Question

I have three tables like below

CREATE TABLE `tbl_rotators` (
  `rid` int NOT NULL,
  `rotator_user` int NOT NULL,
  `rotator_name` varchar(100) NOT NULL,
  `rotator_group` int NOT NULL,
  `rotator_domain` int NOT NULL,
  `rotator_url` text NOT NULL,
  `rotator_mode` int NOT NULL COMMENT '1 = fulfillment 2 = spillover 3= random 4 = sequential',
  `rotator_backup_url` text NOT NULL,
  `rotator_filter_url` text NOT NULL,
  `rotator_spider_url` text NOT NULL,
  `rotator_abuser_settings` int NOT NULL DEFAULT '0' COMMENT '0 = nothing 1 = flag',
  `rotator_anon_setting` int NOT NULL DEFAULT '0' COMMENT '0 = nothing 1 = flag',
  `rotator_bot_setting` int NOT NULL DEFAULT '0' COMMENT '0 = nothing 1 = flag',
  `rotator_spider_setting` int NOT NULL DEFAULT '0' COMMENT '0 = nothing 1 = flag',
  `rotator_server_setting` int NOT NULL DEFAULT '0' COMMENT '0 = nothing 1 = flag',
  `rotator_status` int NOT NULL DEFAULT '0' COMMENT '0 = active 1 = archieved',
  `rotator_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `tbl_rotator_urls` (
  `url_id` int NOT NULL,
  `rotator_id` int NOT NULL,
  `url_user` int NOT NULL,
  `url_name` varchar(250) NOT NULL,
  `url_url` text NOT NULL,
  `url_position` int NOT NULL,
  `max_uc` int NOT NULL DEFAULT '0',
  `max_daily_uc` int NOT NULL DEFAULT '0',
  `bonus_clicks` int NOT NULL DEFAULT '0',
  `min_tier1` int NOT NULL DEFAULT '0',
  `max_tier1` int NOT NULL DEFAULT '0',
  `min_mobile` int NOT NULL DEFAULT '0',
  `max_mobile` int NOT NULL DEFAULT '0',
  `geo` int NOT NULL DEFAULT '0' COMMENT '0 = all 1 = only t1 2 = only eu 3 = custom ',
  `geo_codes` text NOT NULL,
  `url_status` int NOT NULL DEFAULT '0' COMMENT '0 active 1 pause 2 archive',
  `url_created_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `tbl_links_data` (
  `link_data_id` int NOT NULL,
  `link_user_id` int NOT NULL,
  `rotator_id` int NOT NULL DEFAULT '0',
  `link_id` int NOT NULL,
  `ip_provider` varchar(50) NOT NULL,
  `ip` varchar(100) NOT NULL,
  `ip_continent` varchar(50) NOT NULL,
  `ip_country` varchar(50) NOT NULL,
  `ip_isocode` varchar(50) NOT NULL,
  `ip_region` varchar(50) NOT NULL,
  `ip_regioncode` varchar(50) NOT NULL,
  `ip_city` varchar(50) NOT NULL,
  `ip_latitude` varchar(50) NOT NULL,
  `ip_longitude` varchar(50) NOT NULL,
  `ip_proxy` varchar(50) NOT NULL,
  `ip_type` varchar(50) NOT NULL,
  `ip_risk` varchar(50) NOT NULL,
  `browser` varchar(50) NOT NULL,
  `browser_version` varchar(50) NOT NULL,
  `os` varchar(50) NOT NULL,
  `os_version` varchar(50) NOT NULL,
  `device_type` varchar(50) NOT NULL,
  `link_redirect_status` int NOT NULL DEFAULT '0' COMMENT '1= good, 2= proxy/vpn/anon 3= duplicate 4 = bot 5 = spider 6 = abuser 7 = hosting/server',
  `link_redirected_on` text NOT NULL,
  `link_referer` text NOT NULL,
  `link_data_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I am using below query to get records

SELECT r.rid,r.rotator_url,r.rotator_name,r.rotator_mode,r.rotator_status,
       COUNT(u.url_id) as total_url, COUNT(l.link_redirect_status) AS total_clicks, 
       SUM(CASE WHEN l.link_redirect_status = 2 
           OR l.link_redirect_status = 4 THEN 1 ELSE 0 END) AS filtered_clicks,
       SUM(CASE WHEN l.link_redirect_status = 3 THEN 1 ELSE 0 END) AS duplicate_clicks,
       COUNT(DISTINCT CASE WHEN l.link_redirect_status = 1 THEN ip END) AS unique_clicks 
FROM tbl_rotators as r
  LEFT JOIN tbl_rotator_urls AS u ON r.rid = u.rotator_id 
  LEFT JOIN tbl_links_data AS l ON l.rotator_id = r.rid GROUP BY r.rid

and result is like below

However there only one url available in tbl_rotator_urls like below

But its giving total_url as 13725 which is wrong. If I change rotator_id as 5 instead of 4 in table like below

Same query giving me correct result like below

So I think there something wrong with my query but I am not getting idea what I am missing in it, Let me know if anyone here can help me for solve my issue.

Thanks!