Find first questions with answers posted within 24 hours

Question

So, sᴉɔuɐɹɥԀ suggested I write a SEDE query to find first questions with answers posted within 24 hours, and here it is:

WITH QData(OwnerId, CDate)
AS
(
 SELECT
 Q.OwnerUserId [OwnerId]
 ,MIN(Q.CreationDate) [CDate]
 FROM Posts Q
 WHERE Q.PostTypeId = 1 AND Q.OwnerUserId IS NOT NULL
 GROUP BY Q.OwnerUserId
),
FirstQs(QCreationDate, QPostId, QUserId)
AS
(
 SELECT
 CDate [QCreationDate]
 ,Q.Id [QPostId]
 ,OwnerId [QUserId]
 From QData JOIN Posts Q On (Q.CreationDate = CDate AND Q.OwnerUserId = OwnerId)
 WHERE Q.PostTypeId = 1
 GROUP BY OwnerId, Q.Id, CDate
),
FirstQsA(ACreationDate, APostId, AParentId)
AS
(
 SELECT
 MIN(p.CreationDate) [ACreationDate]
 ,MIN(p.Id) [APostId]
 ,p.ParentId [AParentId]
 FROM Posts p, FirstQs
 WHERE (p.PostTypeId = 2 AND p.ParentId = QPostId)
 GROUP BY p.ParentId
)
SELECT QUserId [User Link]
,MIN(QCreationDate) [QCreationDate]
,MIN(ACreationDate) [ACreationDate]
,QPostId[Post Link]
,APostId[Post Link]
FROM
 FirstQs JOIN FirstQsA ON QPostId = AParentId
 WHERE DATEDIFF(HOUR, QCreationDate, ACreationDate) <= 24
GROUP BY QUserId, QPostId, APostId
ORDER BY QUserId

The query can be found here: First Questions with Answers Posted Within 24 Hours.

• PenutReaper: You should really use INNER JOIN instead of just JOIN, it's much clearer.

Answer 1

I have to compliment you on how well your query reads. Your aliases are short enough not to be a PITA to reference, yet descriptive enough to not have to scratch your head about what they mean.

Your CTEs are well formatted, pretty easy to read and have good names. Overall, if I was a DBA and saw that in my code base, I'd think the person who wrote it put some work into making it good.

Well done.

---

Now for a few things to make it even better.

Magic Numbers

Here:

WHERE Q.PostTypeId = 1
-- and later
WHERE (p.PostTypeId = 2

1 and 2 are primary keys in the PostTypes table. This is fine for someone who knows the schema already, but for someone who sees this for the first time, they will have to query the table with those keys to know what they actually mean. So, I recommend using variables, like so:

DECLARE @QuestionPostType INT; 
SET @QuestionPostType = 1;
DECLARE @AnswerPostType INT; 
SET @AnswerPostType = 2;

Then just reference them by name in your query:

WHERE Q.PostTypeId = @QuestionPostType
-- and later
WHERE (p.PostTypeId = @AnswerPostType

---

Proliferation of CTEs

It's easy to get lost in data sets in SQL, and while yours works fine, I think it could be simplified a bit. Take for instance:

WITH QData(OwnerId, CDate)
AS
(
 SELECT
 Q.OwnerUserId [OwnerId]
 ,MIN(Q.CreationDate) [CDate]
 FROM Posts Q
 WHERE Q.PostTypeId = 1 AND Q.OwnerUserId IS NOT NULL
 GROUP BY Q.OwnerUserId
),

You have a whole CTE following to extract basically this:

(Q.CreationDate = CDate AND Q.OwnerUserId = OwnerId)

You could just adapt that in your first CTE and simplify it, kind of like this:

WITH QData(OwnerId, CDate)
AS
(
 SELECT
 Q.OwnerUserId [OwnerId]
 ,MIN(Q.CreationDate) [CDate]
 FROM Posts Q
 WHERE Q.PostTypeId = 1 AND Q.OwnerUserId IS NOT NULL
 AND (Q.CreationDate = CDate AND Q.OwnerUserId = OwnerId)
 GROUP BY Q.OwnerUserId
),

Take advantage of joins

Many think of joins as basically just linking keys between one table and another. But, you can use joins to filter data, and keep related conditions/filters together in one join, rather than splitting them up in a bunch of CTEs or where clauses.

For example, you could extract some of the Q & A logic into a join. Whether or not it is desirable, it-depends. But for the sake of example, this:

FROM
 FirstQs JOIN FirstQsA ON QPostId = AParentId

...along with your other filters which are included in your CTEs, could be something like:

FROM
 FirstQs 
 JOIN FirstQsA 
 ON QPostId = AParentId
 AND Q.PostTypeId = 1 -- or @QuestionPostType
 AND A.PostTypeId = 2 -- or @AnswerPostType