# Get yesterday's top 10 posts by likes or views

**URL:** https://meta.discourse.org/t/topic/382918
**Category:** Support
**Created:** [September 17, 2025, 7:05am UTC](https://meta.discourse.org/t/topic/382918 "2025-09-17T07:05:48Z")
**Posts on this page:** 1
**Showing post:** 4

<div class="post-metadata">

### Author: ![nat](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/nat/32/235063_2.png) [@nat](https://meta.discourse.org/u/nat)
#### Post date: [September 19, 2025, 6:43pm UTC](https://meta.discourse.org/t/topic/382918/4 "2025-09-19T18:43:33Z")

</div>

You should enable it, then you’ll be able to make specific queries scoped to the past day (yesterday).

### Query for Top 10 Posts by Likes from Yesterday

```sql
-- Top 10 Posts by Likes from Yesterday
WITH yesterday_actions AS (
  SELECT 
    post_id,
    COUNT(*) AS like_count
  FROM post_actions
  WHERE 
    created_at::date = CURRENT_DATE - 1
    AND post_action_type_id = 2 -- Like action type
  GROUP BY post_id
)

SELECT 
  p.id AS post_id,
  t.id AS topic_id,
  t.title AS topic_title,
  p.post_number,
  u.username AS author,
  ya.like_count AS likes_yesterday
FROM yesterday_actions ya
JOIN posts p ON p.id = ya.post_id
JOIN topics t ON t.id = p.topic_id
JOIN users u ON u.id = p.user_id
ORDER BY likes_yesterday DESC
LIMIT 10

```

### Query for Top 10 Posts by Views from Yesterday

```sql
-- Top 10 Topics by Views from Yesterday
WITH yesterday_topic_views AS (
  SELECT 
    topic_id,
    COUNT(*) AS view_count
  FROM topic_views
  WHERE viewed_at::date = CURRENT_DATE - 1
  GROUP BY topic_id
)

SELECT 
  t.id AS topic_id,
  t.title,
  u.username AS creator,
  ytv.view_count AS views_yesterday
FROM yesterday_topic_views ytv
JOIN topics t ON t.id = ytv.topic_id
JOIN users u ON u.id = t.user_id
ORDER BY views_yesterday DESC
LIMIT 10

```

(generated using AI bot!)

---

_[View the full topic](https://meta.discourse.org/t/topic/382918)._