# Likes by 'team'

**URL:** https://meta.discourse.org/t/likes-by-team/274999
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [May 1, 2016, 6:48pm UTC](https://meta.discourse.org/t/likes-by-team/274999 "2016-05-01T18:48:22Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![fefrei](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/fefrei/32/119538_2.png) [@fefrei](https://meta.discourse.org/u/fefrei)
#### Post date: [May 1, 2016, 6:48pm UTC](https://meta.discourse.org/t/likes-by-team/274999/1 "2016-05-01T18:48:22Z")

</div>

**Likes from the team**

This query assumes there is a group called _team_, and gives you the likes other users have received, split into likes from the team and likes from others:

```sql
SELECT
    pl.user_id,
    SUM(pl.team_likes) as team_likes,
    SUM(pl.student_likes) as student_likes,
    SUM(pl.team_likes + pl.student_likes) as likes
FROM (
    SELECT -- count likes per post
        p.id as post_id_workaround,
        p.user_id as user_id,
        (
            SELECT count(*)
            FROM post_actions pa
            WHERE
                pa.post_id = p.id
                AND post_action_type_id = (
                            SELECT id FROM post_action_types WHERE name_key = 'like'
                )
                AND pa.user_id IN (
                    SELECT gu.user_id
                    FROM group_users gu
                    WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike 'team' ) 
                )
        ) as team_likes,
        (
            SELECT count(*)
            FROM post_actions pa
            WHERE
                pa.post_id = p.id
                AND post_action_type_id = (
                            SELECT id FROM post_action_types WHERE name_key = 'like'
                )
                AND pa.user_id NOT IN (
                    SELECT gu.user_id
                    FROM group_users gu
                    WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike 'team' ) 
                )
        ) as student_likes
    FROM badge_posts p
) AS pl
WHERE pl.user_id NOT IN (
    SELECT gu.user_id
    FROM group_users gu
    WHERE gu.group_id = ( SELECT id FROM groups WHERE name ilike 'team' ) 
)
GROUP BY pl.user_id
ORDER BY likes DESC

```