# Off-topic posts that deserved their own topic

**URL:** <https://meta.discourse.org/t/off-topic-posts-that-deserved-their-own-topic/275139>\
**Category:** Data & reporting\
**Tags:** moderation, sql-query\
**Created:** [5월 27, 2016, 3:47오전 UTC](https://meta.discourse.org/t/off-topic-posts-that-deserved-their-own-topic/275139 "2016-05-27T03:47:44Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![Mittineague](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mittineague/32/114259_2.png) [@Mittineague](https://meta.discourse.org/u/Mittineague)\
**Post date:** [5월 27, 2016, 3:47오전 UTC](https://meta.discourse.org/t/off-topic-posts-that-deserved-their-own-topic/275139/1 "2016-05-27T03:47:44Z")

</div>

While working on this

> [@With the new right gutter, where should "Reply as Linked Topic" go?](https://meta.discourse.org/t/with-the-new-right-gutter-where-should-reply-as-linked-topic-go/44815/22):
>
> I came up with a couple of queries. The one finds “Reply as” topics, the other both “Reply as” and “copied link” topics. WITH op\_posts AS ( SELECT id , raw FROM posts WHERE post\_number = 1 ) SELECT COUNT(id) FROM op\_posts WHERE raw LIKE '%Continuing the discussion from%' . WITH op\_posts AS ( SELECT id , raw FROM posts WHERE post\_number = 1 ) SELECT COUNT(topic…

I was looking for a way to find topics that were created as a result of a moderator splitting posts into a new topic. (i.e. off-topic posts that deserved their own topic)

It wasn’t as easy as I had hoped. I looked at various “topic” and “post” tables with no success.

Being sure I could find a lead to what I needed in Admin → Logs → Staff Actions I was disappointed to find that “split” data was not there (not that it should, hopefully it doesn’t happen all that often)

The only place I could find the id of a topic created this way is in “small-action” posts as part of a link.

Getting the id portion of the string to an integer that can be used is a bit involved.

regexp\_matches returns a string array  
array\_to\_string converts the array to a string  
CAST converts the string to an integer

```plaintext
WITH new_topics AS (
 SELECT 
 CAST( array_to_string(regexp_matches(posts.raw, '([\d]+)(?:\))$', 'g'), '') AS integer) AS new_topic_id
 FROM posts 
 WHERE action_code LIKE 'split_topic'
 AND raw LIKE '%posts were split to a new topic%'
)
SELECT topics.title 
FROM topics, new_topics
WHERE topics.id = new_topics.new_topic_id

```