Hey, folks! How are you doing?
I’m kinda new to building queries in SQL for my community, so I’m stuck on one issue.
I have to build a query where I get data related to the threads created by a specific group in the community. This is what I got so far:
-- [params] -- int :months_ago = 1 WITH query_period as ( SELECT date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' as period_start, date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' + INTERVAL '1 month' - INTERVAL '1 second' as period_end ) SELECT t.id as topic_id, t.title, u.username, t.views, t.posts_count FROM topics t RIGHT JOIN query_period qp ON t.created_at >= qp.period_start AND t.created_at <= qp.period_end INNER JOIN users as u ON t.user_id = u.id WHERE t.user_id > 0 and u.username in ('maria.narvaez', 'Joao.Caique', 'camila.ochoa', 'Caroline.Freitas','dante.chacon', 'maria.novaes', 'FernandaVivacqua', 'gabriea.marta', 'IgorMello', 'Jay', 'joaostoky', 'Julio.Torquato', 'Luis_Quesada', 'maiara.zotelli', 'natalia.bispo', 'rbussola', 'talissa','tfgouveia', 'Victor.Ferreira')
I got almost everything I needed. The only data remaining is the quantity of likes_received in these threads. But, I gotta count all likes, excluding the likes from one specific group.
How can I do it? I googled some solutions and I found things regarding CTEs, however I don’t know how to use them properly.
I would appreciate any help or hint,
thanks in advance.