7

Please login or register to vote for this query.

(click on this box to dismiss)

Recommended value for MinCountOfQuestionsPerBadge is 25, otherwise the query will run for too long. This means we will only look at tags which have >25 questions or more in them.

Q&A about the site for students, teachers, and linguists wanting to discuss the finer points of the Japanese language

--
--
   

DECLARE @MinCount int
select @MinCount = ##MinCountOfQuestionsPerBadge?25##

SELECT id, tagname, count, -999 AS 'creatorid'  -- Can't use -1 as that's community
INTO   #t1 
FROM   tags 
WHERE  count > @MinCount
GO
-- DisplayName
DECLARE @userid int
SELECT  @userid=##UserId##
DECLARE @id int
DECLARE @creatorid int
DECLARE @tagname nvarchar (25)

WHILE (EXISTS (SELECT 1 FROM #t1 WHERE creatorid = -999))
BEGIN
    SELECT @id=min(id) 
    FROM   #t1 
    WHERE  creatorid = -999
    
    SELECT @tagname=tagname 
    FROM   #t1 
    WHERE  id=@id
    
    SELECT @creatorid = NULL
    
    SELECT @creatorid = userid
    FROM   PostHistory PH1
    WHERE  text like '%<'+@tagname+'>%'
     AND   PostHistoryTypeId IN (3,6,9)
     AND   userid = ##UserId##
     AND   NOT EXISTS (SELECT 1 
                       FROM   PostHistory PH2
                       WHERE  PH2.text like '%<'+@tagname+'>%'
                        AND   PH2.PostHistoryTypeId in (3,6,9)
                        AND   PH1.CreationDate > PH2.CreationDate)


    SELECT @creatorid = COALESCE(@creatorid, -1000) -- in case it's null, so we don't get infinite loop
    UPDATE #t1 
    SET    creatorid = @creatorid
    WHERE  id=@id
    
END
go

DECLARE @badge int

SELECT @badge=count(1) from badges
WHERE  Name   = 'Taxonomist'
  AND  userid = ##UserId##
  
IF (@badge > 0)
BEGIN
    INSERT #t1
    VALUES
    (0, '!Already have Taxonomist!', 10000, ##UserId##)
END

SELECT tagname, count
      , CASE WHEN count < 50 THEN 50-count ELSE 0 END AS 'Questions Left Till Taxonomist'
      , id AS 'TagId', creatorid as 'UserId'
FROM   #t1
WHERE  creatorid = ##UserId##
order by count DESC
DROP TABLE #t1
go

fork query

edited dec 3 12

created dec 3 12 D