0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for pro webmasters

DROP TABLE IF EXISTS #got --[See Kate Style Note 1] --[See New Skill 1b]

SELECT
 Post_Id = p.Id --[See Kate Style Note 2]
,Tag_Id = t.Id --[See Kate Style Note 3]
,p.Score
,p.Body
,p.Title
,p.Tags
INTO #got --[See Coding Tip 1] --[See New Skill 1a]
FROM tags t
JOIN PostTags pt ON pt.TagId = t.Id --[See New Skill 2]
JOIN Posts p ON p.Id = pt.PostId 
WHERE 1=1 --[See Kate Style Note 4]
  AND   (
        t.TagName LIKE '%game%of%thrones%'
        OR
        t.TagName LIKE '%a%song%of%ice%and%fire%'
        ) --[See Coding Tip 2]
        
        
        
DROP TABLE IF EXISTS #ff

SELECT
 * --[See New Skill 3]
INTO #ff
FROM #got g
WHERE 1=1
  AND CASE  WHEN g.Title LIKE '%fan%fic%' THEN 1 --[See New Skill 4]
            WHEN g.Tags LIKE '%fan%fic%' THEN 1
            WHEN g.Body LIKE '%fan%fic%' THEN 1 --210113 ksrosch added, I hadn't   --[See Kate Style Note 5]
                                                --initially included a review of 
                                                --the Post's body, but it's essentially 
                                                --an elaboration of the title so I 
                                                --think we should include it
            ELSE 0
            END = 1 --[See Coding Tip 3]
            
            
            
SELECT
 CountOfPosts = COUNT(*) --[See New Skill 5b]
,AverageScore = AVG(f.Score) --[See New Skill 5a]
FROM #ff f

fork query

edited jan 16 21

created jan 13 21 jon.doe35017