MySQL popular rows based on different time levels

Question

I have 2 tables: photo and like. I am trying to get popular photos based on number of likes in a day. So basically something like 'popular today'.

SELECT
  p.id AS id, COUNT(li.id) AS total_likes \
FROM `photo` p \
  LEFT JOIN `like` li \
    ON p.id = li.photo_id \
WHERE
  li.date > DATE_SUB(CURDATE(), INTERVAL 1 DAY) \
GROUP BY \
  p.id

This works well when there are enough number of likes today. But if there are no likes in the past day, it will return no records.

I also want to change this a little. Is it possible to make levels? For example:
Rank photos based on multiple days:

1. Get photos based on how many likes today
2. Get photos based on how many likes for last week
and so on...

So basically what this does is, let's say we need to get 30 items. First it will try to get rows based on how many likes there were today. It may be any number 20, 15 etc. Then it will get the remaining rows needed to get to a total of 30 but now will sort based on how many likes in a week.

So something like:
SELECT FROM photo SORT BY likes today, likes in a week ...

Thanks for your help!

Answer 1

You can calculate different counts based on CASE, e.g. the likes of the last 30 days with counts for last day. last week and last 30 days:

SELECT
  p.id AS id
  ,COUNT(CASE WHEN li.DATE > DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN li.id END) AS daily_likes
  ,COUNT(CASE WHEN li.DATE > DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN li.id END) AS weekly_likes
  ,COUNT(li.id) AS total_likes
FROM `photo` p
JOIN `LIKE` li
    ON p.id = li.photo_id
WHERE
  li.DATE > DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY
  p.id
ORDER BY daily_likes DESC, weekly_likes DESC, total_likes DESC
LIMIT 30

I don't know on which definition your limit is based on, it might be something like

SELECT *
FROM
 (
   SELECT
     p.id AS id
     ,COUNT(CASE WHEN li.DATE > DATE_SUB(CURDATE(), INTERVAL 1 DAY) THEN li.id END) AS daily_likes
     ,COUNT(CASE WHEN li.DATE > DATE_SUB(CURDATE(), INTERVAL 7 DAY) THEN li.id END) AS weekly_likes
     ,COUNT(li.id) AS total_likes
   FROM `photo` p
   JOIN `LIKE` li
       ON p.id = li.photo_id
   WHERE
     li.DATE > DATE_SUB(CURDATE(), INTERVAL 30 DAY)
   GROUP BY
     p.id
  ) AS dt
ORDER BY
   case when daily_likes > 20 then daily_likes else 0 end desc,
   case when weekly_likes > 100 then weekly_likes else 0 end desc,
   total_likes DESC
LIMIT 30