Analyze the amount of topics

Dev
1

I need some help with this query.

The idea is to be able to analyze the amount of topics that have a certain user, to get specific statistics on the behavior of the user.

Of the total of topics created, I need to get:

  • The number of topics closed
  • The number of issues with solution.
  • The number of topics closed.
  • The number of archived topics.

The idea is to get something like this:
image

v0.1

--[params]
--string :user_name = SidV
WITH usuario AS ( SELECT users.id, 
            users.username AS u_u 
            FROM users )
,    stats AS ( SELECT user_stats.user_id,
            user_stats.topic_count AS total
            FROM user_stats )
,    di AS ( SELECT directory_items.user_id, 
            directory_items.topic_count AS di_tc,
            directory_items.period_type AS di_pt
            FROM directory_items )

SELECT 
stats.user_id, 
stats.total
FROM usuario, stats, di
WHERE usuario.u_u ILIKE CONCAT('%', :user_name, '%') 
AND di.user_id = stats.user_id 
AND usuario.id = di.user_id
AND di.di_pt = 1
ORDER BY stats.user_id, di.di_pt 
LIMIT 10

Can somebody help me please? :pray: