Different counting methods for Likes: user_stats vs Badges [maybe bug]

UX
1

We have a user who has over 1400 submitted Likes according to the User Summary. And also over 600 received Likes. Both for several days. He still has not received the Badge Emphatic (500 Likes received and 1000 given)

After some searching I found out that the Likes are counted differently here. In Summary the query is:

SELECT COUNT(*) FROM "user_actions" WHERE "user_actions"."user_id" = 15 AND "user_actions"."action_type" = 2
"Likes Revived: " 638

SELECT COUNT(*) FROM "user_actions" WHERE "user_actions"."user_id" = 15 AND "user_actions"."action_type" = 1

“Likes given”: 1471

For the badge is the condition as follows, so he should get the badge.

      SELECT us.user_id, current_timestamp AS granted_at
      FROM user_stats AS us
      INNER JOIN posts AS p ON p.user_id = us.user_id
      WHERE p.like_count> 0
        AND us.likes_given> = 1000
        AND (: backfill OR us.user_id IN (: user_ids))
      GROUP BY us.user_id, us.likes_given
      HAVING COUNT (*)> 500

The query

 SELECT us.user_id, count (*) cnt_likes_received, us.likes_given
      FROM user_stats AS us
      INNER JOIN posts AS p ON p.user_id = us.user_id
      WHERE p.like_count> 0
        AND us.user_id = 15
        AND us.likes_given >= 1000
      GROUP BY us.user_id, us.likes_given;
user_id cnt_likes_received likes_given
15 393 1462

This suggests that either user_stats is completely wrong, or that the Likes are counted differently.
I always get reports about this, because badges seem very important to our users.

Either “fix” user_stats or change the query for the Badge.

Edit: I have no pending or broken sidekiq jobs. We use the latest test-passed branch from today.

1 Like
2

Any ideas here @sam?

3

One moment, what is the user_stat record for said user? keep in mind user_stat is a cache table, it is not the source of truth.

4

SELECT * FROM user_stats where user_id = 15
gives

2017-03-01 user_stats.png2103Ă—70 13.3 KB

5

So wait … the issue is

1474 vs 1462
641 vs 638

Seems fairly close and a consistency job should eventually repair it.

6

Yes but the badge query gives an other number:

Original Query is

  SELECT us.user_id, current_timestamp AS granted_at
  FROM user_stats AS us
  INNER JOIN posts AS p ON p.user_id = us.user_id
  WHERE p.like_count > 0
    AND us.likes_given > = 1000
    AND (: backfill OR us.user_id IN (: user_ids))
  GROUP BY us.user_id, us.likes_given
  HAVING COUNT (*) > 500

And

SELECT us.user_id, count (*) cnt_likes_received, us.likes_given
      FROM user_stats AS us
      INNER JOIN posts AS p ON p.user_id = us.user_id
      WHERE p.like_count> 0
        AND us.user_id = 15
        AND us.likes_given >= 1000
      GROUP BY us.user_id, us.likes_given;

gives

7

ahh well that would only be counting public posts, it is not counting PMs or posts in private categories

3 Likes