SQLite 3.27

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

with cte1 as

(
select 'a' as videoid, 1 as channelid union all
select 'b' as videoid, 1 as channelid union all
select 'c' as videoid, 2 as channelid union all
select 'd' as videoid, 2 as channelid)

,cte2 as

(select 'c1' as commentid, null as replyid, 'a' as videoid, 1 as videoid_channelid, 'rrtt' as textOriginal union all
select 'c2' as commentid, null as replyid, 'a' as videoid, 1 as videoid_channelid, 'tedgst' as textOriginal union all
select null as commentid, 'r1.c1' as replyid, 'b' as videoid, 1 as videoid_channelid, 'tsdfgest' as textOriginal union all
select null as commentid, 'r1.c2' as replyid, 'b' as videoid, 1 as videoid_channelid, 'teoist' as textOriginal union all
select 'c5' as commentid, null as replyid, 'b' as videoid, 1 as videoid_channelid, 'teddst' as textOriginal union all
select 'c6' as commentid, null as replyid, 'c' as videoid, 2 as videoid_channelid, 'tertfdst' as textOriginal union all
select 'c7' as commentid, null as replyid, 'd' as videoid, 2 as videoid_channelid, 'tggeest' as textOriginal)

select a.*, b.channel_comment_count

from 
(select v.videoid,v.channelid,count(c.textoriginal) as video_comment_count
from cte1 v join cte2 c on v.videoid = c.videoid and v.channelid = c.videoid_channelid 
group by v.channelid, v.videoid) a

inner join

(select channelid,count(c.textoriginal) as channel_comment_count
from cte1 v join cte2 c on v.videoid = c.videoid and v.channelid = c.videoid_channelid 
group by v.channelid) b

on a.channelid=b.channelid

videoid	channelid	video_comment_count	channel_comment_count
a	1	2	5
b	1	3	5
c	2	1	2
d	2	1	2

with cte1 as

(
select 'a' as videoid, 1 as channelid union all
select 'b' as videoid, 1 as channelid union all
select 'c' as videoid, 2 as channelid union all
select 'd' as videoid, 2 as channelid)

,cte2 as

,cte3 as

(select v.*,c.textOriginal from cte1 v join cte2 c on v.videoid = c.videoid and v.channelid = c.videoid_channelid)

select a.*,b.channel_comment_count
from 
(select videoid, channelid, count(textoriginal) as video_comment_count
from cte3 group by channelid, videoid) a
inner join
(select channelid,count(textoriginal) as channel_comment_count
from cte3 group by channelid) b
on a.channelid=b.channelid

videoid	channelid	video_comment_count	channel_comment_count
a	1	2	5
b	1	3	5
c	2	1	2
d	2	1	2