Sorting by views per day / week / month - how to build a query?

Question

MySQL

Hi guys.
You need to sort Wordpress posts by views for 24 hours, 7 days, 30 days.
Given:

• WP Post Table
  
• wp_post_views table with two fields: ID (post ID) and time (stores the date and time of the view)
  

Actually, you need to build an SQL query that will sort the posts from the wp_posts table by the number of rows with the post ID in the wp_post_views table for the required period.
Any ideas how this can be done? Tried to do through JOIN, GROUP BY and COUNT. Nothing worked for me ... In SQL, I'm a complete layman.
Thanks in advance :)
Found this solution:

SELECT `ID`, `post_title` FROM `wp_posts` AS t1
INNER JOIN `wp_post_views` AS t2 USING(`ID`)
WHERE t2.time >= CURDATE() - INTERVAL 30 DAY /* или 1 DAY, или 7 DAY*/
GROUP BY `ID`
ORDER BY COUNT(t2.ID) DESC 
LIMIT 10

Execution result:

It seems to work, but it's not certain ... Mb will come in handy for someone :)

Answer 1

Something like this, if I'm not mistaken:

SELECT
      `ID`
    , `post_title`
    , pv30.cnt_month
    , COALESCE(pv7.cnt_week, 0) cnt_week
    , COALESCE(pv1.cnt_day, 0) cnt_day
  FROM `wp_posts` AS p
  INNER JOIN (
    SELECT id, count(id) cnt_month
      FROM `wp_post_views` pv
      WHERE pv.time >= CURDATE() - INTERVAL 30 DAY
      GROUP BY pv.id
    ) AS pv30
    ON pv30.id = p.id
  LEFT JOIN (
    SELECT id, count(id) cnt_week
      FROM `wp_post_views` pv
      WHERE pv.time >= CURDATE() - INTERVAL 7 DAY
      GROUP BY pv.id
    ) AS pv7
    ON pv7.id = p.id
  LEFT JOIN (
    SELECT id, count(id) cnt_day
      FROM `wp_post_views` pv
      WHERE pv.time >= CURDATE() - INTERVAL 1 DAY
      GROUP BY pv.id
    ) AS pv1
    ON pv1.id = p.id
  ORDER BY pv30.cnt_month DESC, pv7.cnt_week DESC, pv1.cnt_day DESC
  LIMIT 10;

Answer 2

There is nowhere to check. I would venture to guess:
--24 hours
select * from wp_posts wp
inner join wp_post_views wv on wp.id = wv.id
where time > DATE_SUB(CURDATE(), INTERVAL 24 HOUR);
-- 7 days
select * from wp_posts wp
inner join wp_post_views wv on wp.id = wv.id
where time > DATE_SUB(CURDATE(), INTERVAL 168 HOUR);
-- 30 days
select * from wp_posts wp
inner join wp_post_views wv on wp.id = wv.id
where time > DATE_SUB(CURDATE(), INTERVAL 720 HOUR);