# SQL query in Data Explorer to pull latest topics?

**URL:** https://meta.discourse.org/t/sql-query-in-data-explorer-to-pull-latest-topics/63908
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [4 Giugno 2017, 3:47pm UTC](https://meta.discourse.org/t/sql-query-in-data-explorer-to-pull-latest-topics/63908 "2017-06-04T15:47:22Z")
**Posts on this page:** 1
**Showing post:** 6

<div class="post-metadata">

### Author: ![sunnyt7](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sunnyt7/32/71920_2.png) [@sunnyt7](https://meta.discourse.org/u/sunnyt7)
#### Post date: [5 Giugno 2017, 3:21am UTC](https://meta.discourse.org/t/sql-query-in-data-explorer-to-pull-latest-topics/63908/6 "2017-06-05T03:21:42Z")

</div>

So I spent a few hours this afternoon learning SQL queries. Lol. Here’s where I got:

```
SELECT
t.last_post_user_id, ua.custom_upload_id, u.username, u.username_lower, t.title, t.slug, t.id, t.category_id, c.name, c.color, t.posts_count, t.last_posted_at
FROM
topics t
LEFT JOIN categories c ON c.id = t.category_id
LEFT JOIN users u ON u.id = t.last_post_user_id
LEFT JOIN user_avatars ua ON ua.user_id = t.last_post_user_id
WHERE
t.archetype = 'regular'
AND
t.last_posted_at IS NOT NULL
AND
t.visible IS TRUE
AND
t.deleted_at IS NULL
ORDER BY
t.last_posted_at DESC
LIMIT
10

```

And then using Twig Anything, am now displaying this on my site (haven’t stylized it yet):

 ![](https://global.discourse-cdn.com/meta/original/3X/b/0/b0ed8ee5fdf936ea730317567b42dc390a724215.png)

Almost there. Some issues I’ve run into:

- The timestamp is tough. I can manually set a timezone, but I wish I could have it auto-set per user. Better yet, it would just say something like “8h” or “8 hours ago”. Not too sure how to do that.
- I wanted to use Replies instead of Posts, but reply\_count in the database doesn’t seem to be too accurate? It was showing a 1 instead of a 4 for the first topic. What does that field mean?

---

_[View the full topic](https://meta.discourse.org/t/sql-query-in-data-explorer-to-pull-latest-topics/63908)._