I have table called post_likes which contain the following fields
- id (int)
- post_id (int)
- type (enum) (FB_LIKES or LINKEDIN_LIKES)
- user_hash (varchar) (FB or Linkedin unique identifier)
- like_count (int)
- created_date (timestamp)
Created_at is based on UTC time zone.
Our business logic is that user can add LIKE one time per day (based on PST timezone) for the post. Meaning, 1 LIKE from FB and 1 LIKE from Linkedin.
I have a condition in application side to check and restrict peoples who are trying to give like same day. Somehow, some users added duplicate LIKES for the same day (it might be code or web server issue. Just leave this for now).
My current query is as follows to get the count of each post
SELECT `id`,
`title`,
Sum(IF(`type` = 'FB_LIKES', like_count, 0)) AS fb_like_counts,
Sum(IF(`type` = 'LINKEDIN_LIKES', like_count, 0)) AS linkedin_like_counts,
Sum(`like_count`) AS total_like_counts
FROM `post_likes`
GROUP BY `title`
ORDER BY `total_like_counts` DESC;
Now, I want to get DISTINCT counts. because some of the post has duplicate likes for the same day. Here same user can like next day.
- So need to add a condition based on PST timezone and exclude duplicate likes for the same day
Note: Sorry for my bad english and thanks in advance
Sorry for the late update. Below is my final query which gives the correct result