We have two different tables, one for topics opened and the other for users.
In the system, the id information of the user who opens the topic is also kept in the table where the topics are found.
In the Topics Table
There are fields like id, title, content, published, user_id.
In the Users Table
There are fields like u_id, username, password, role.

We want to display users with roles 1, 2, and 3 in descending order of the number of topics published by them.

I had done this with a very long method, separately obtaining user ids and the number of topics of those users from where the topics are located, assigning them to an array variable, sorting them, and looping through them with foreach so that only three users would appear in a long series of queries. I was looking for a shorter method and I solved it thanks to my brother Kerim Yılmaz (Ayazoğlu).

A single SQL query

select distinct(b.user_id), u.username, 
(select count(id) from topics where user_id=u.u_id) as total
 from users u inner join topics b on b.user_id=u.u_id
where b.published=1 and u.role=1 or u.role=2 or u.role=3 order by total desc limit 3

Let me explain the query, we did table joining, I explained table joining in this post. We're fetching data without duplicates using distinct.
We selected users who have topics in the topics table, and we selected only the fields we want to print on the screen from the database, username, the number of topics, and user ids.
While performing the select operation, we used another select query inside parentheses, here we used count to get the numbers of topics, and we specified which user's topic count we will get with the where condition, then we transferred these numbers to a field named total and sorted them in reverse order according to the total field, selecting only three people.

When you run the query in phpMyAdmin, it returns a result like the image below: