Cannot figure out writing a compound SQLquery to the Oracle database

Please help me sort out the request: Develop a query to calculate the number of news, written by each author and the most popular tag, referred to author news. All these information must be output in one single result set.

I wrote the first part of the request, it displays the amount of news for each author:

SELECT news_author.author_id AS "Author ID", COUNT(*) AS "Amount of news"
FROM news
JOIN news_author ON id = news_author.news_id 
JOIN news_tag ON id = news_tag.news_id
GROUP BY news_author.author_id
ORDER BY news_author.author_id;

Please tell me how to make a request for the most popular tag, referred to author news and combine these two samples into one result set.

You can use standard ANSI SQL features or Oracle SQL bonuses.

The table scheme is attached.

Answer

The most popular tag has a name in statistics, called the mode. And, Oracle has an aggregation function stats_mode() to calculate it. So, you can use:

SELECT na.author_id AS "Author ID",
       COUNT(DISTINCT n.id) AS num_news,
       STATS_MODE(nt.tag_id)
FROM news n JOIN
     news_author na
     ON n.id = na.news_id JOIN
     news_tag nt
     ON n.id = nt.news_id
GROUP BY na.author_id
ORDER BY na.author_id;