5

I'm writing a query to find old questions without much answer attention, to help combat the xkcd "Wisdom of the Ancients" problem. I can filter out closed posts with p.ClosedDate IS NULL, but I'm getting merged questions in the results — a lot of 'em. How can I filter these out too?

5
  • Can you point to some results of your query that are merged questions? (I spot-checked a couple dozen and didn't see any.) If you can, we might be able to determine an indirect way to detect merged posts. Dec 18, 2011 at 21:33
  • @BrockAdams — run it on Photo-SE, and look at the top 3.
    – mattdm
    Dec 18, 2011 at 21:36
  • Well, that was a bust. There's nothing in the question record or comments that hints that the Q's are merged. ... Looks like the only recourse is to feature-request that PostHistoryTypes​ value 18 (Question Merged) data be put back in SEDE. Dec 18, 2011 at 21:49
  • Thanks @Brock. Should I do that here, or a new question?
    – mattdm
    Dec 18, 2011 at 22:00
  • I'd vote for a new question. Maybe reference this Q and meta.stackexchange.com/questions/102638/… . Dec 18, 2011 at 22:16

4 Answers 4

2

I executed the following query, and got the following output:

SELECT Id, Name FROM PostHistoryTypes​

screenshot

screenshot

You can search for a question for which one of the history entries (the table is "PostHistory") contains 18 as value of PostHistoryTypeId.

I tried executing the following query on a site with much less questions than Stack Overflow (Drupal Answers):

SELECT
    p.Id AS [Post Link], t.name as [Edit type]
FROM
    Posts p
INNER JOIN 
    PostHistory h ON h.PostId = p.Id
INNER JOIN 
    PostHistoryTypes t ON t.Id = h.PostHistoryTypeId 
WHERE 
    p.ParentID IS NULL AND t.Id > 10​

The only case where I found a "Post Deleted" entry is when the question has been successively undeleted; this would confirm what Jeff Mercado is saying: Entries about deletion, and merging don't appear in SEDE.

screenshot

5
  • 18, not 17. Thanks — this looks promising. Except: data.stackexchange.com/photography/q/122085/… — there don't appear to be any history entries of that type in the data explorer database. Stripped out? Or am I doing something wrong?
    – mattdm
    Dec 18, 2011 at 20:24
  • Whoops… I don't know why, but I read migrated instead of merged.
    – apaderno
    Dec 18, 2011 at 20:26
  • Well, it's still on the right track, except for how it doesn't work. :)
    – mattdm
    Dec 18, 2011 at 20:27
  • @mattdm: Unfortunately like deletions, it appears merges will not appear in the SEDE. Dec 18, 2011 at 20:31
  • The merged posts are still there, though; you just can't tell if that's why they have no answers. Shame.
    – mattdm
    Dec 18, 2011 at 20:54
1

See this "History-type mystery" query and What are these mysterious values for PostHistoryType?

What that shows is that SEDE did once record at least 3661 merged questions, but now that information has been expunged!

From Kevin Montrose:

Most of these weren't meant to be included... They probably won't be included in the next dump.

So, there's no way to (easily) detect merged questions unless you can get that info feature-requested back into SEDE.

1

I found a workaround — Merged posts are apparently also Locked in the post history, so PostHistoryType 14 works as well as 18. That means this query:

https://data.stackexchange.com/photography/query/56730/find-questions-ripe-for-the-revival-and-necromancer-badges

now does what it's supposed to do.

0

Based on mattdm workaround (looking for Locked posts instead of Merged posts), and kiamlaluno base query formatting, I came up with:

SELECT
    p.Id AS [Post Link], p.CreationDate
FROM
    Posts p
INNER JOIN
    PostHistory h ON h.PostId = p.Id
WHERE
    h.PostHistoryTypeId = 14
AND
    p.ParentId is null
AND
    p.AnswerCount = 0
Group By
    p.Id, p.CreationDate
ORDER BY
    p.CreationDate

Currently, my query returns 1850 likely merged questions.

Unfortunately, it also contains Migrated questions.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .