SODE query returns no rows-CodePudding

I have the following query on https://data.stackexchange.com/stackoverflow

SELECT TOP 10 * FROM Posts
WHERE PostTypeId = 2 
  AND CreationDate > '2022-05-06'
  AND CommentCount = 3
  AND Tags LIKE '%php%'
ORDER BY CreationDate DESC

I am trying to get answers that were posted today in the php tag that also have 3 comments.

But all I receive is:

0 rows returned in 51274 ms

CodePudding user response：

I'm sure that SE Data Explorer is not always up to date, but the problem seems to be elsewhere.
The column Tags is null for answers.
If you want to get the tags you must query the question which you can get by the column ParentId.

Use a self join:

SELECT TOP 10 p1.* 
FROM Posts p1 INNER JOIN Posts p2
ON p2.Id = p1.ParentId
WHERE p1.PostTypeId = 2 
  AND p2.Tags LIKE '%php%' 
  AND p1.CommentCount = 3
  -- AND <your date condition here>
ORDER BY p1.CreationDate DESC

See the demo.

CodePudding user response：

First, your query seems extremely selective. Give me all the answers to PHP questions from today where the answer has exactly 3 comments? I don't know that you're ever going to find a whole lot of those. Maybe if you change your criteria to CommentCount >= 3 (and go after tags the right way), but even with those fixes, comments on answers don't often get that high (or stay around very long).

Second, SEDE is updated weekly, on Sundays. See this question, this question (which links to this query, which you can use to monitor status), and this question.

The SEDE refresh is an extensive operation because the data is transformed in various ways (the structure is simplified to make your queries easier, and the data is sanitized to remove any PII). So on a Saturday, no, you're not going to find any posts there from today.

You can just check this query to see why your WHERE clause is additionally too selective:

SELECT MAX(CreationDate) FROM Posts;

Once you've picked date criteria that work, you can fix the other issues in the query.