How to count likes_received excluding a specific group

Community Building Data & reporting

AI-generated summary

Ítalo_Dias is seeking help with a SQL query to count likes_received in threads created by a specific group, excluding likes from another specific group. They have a query that retrieves topic data, but need assistance with incorporating the likes data while excluding likes from the specified group.

1

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.

1 Like