What tag brought a user to the site?

1

Please login or register to vote for this query.

(click on this box to dismiss)

This will figure out some statistics as to what tags bring users to a site, and how long they stick around.

Christianity Meta

Q&A about the site for committed Christians, experts in Christianity and those interested in learning more

WITH UserPosts AS 
(
    SELECT 
	   RN = ROW_NUMBER() OVER (Partition By OwnerUserId ORDER BY CreationDate),
	   OwnerUserId as SEUser,
       Id as PostId
    FROM 
	   Posts
),
UserQuestionCount AS 
(
    SELECT 
	   count(Id) AS PostCount, 
	   OwnerUserId AS Id
    FROM 
	   Posts
    GROUP 
	   BY OwnerUserId
 )
SELECT TOP 100 
    t.tagname, 
    count(u.id) AS UsersCt, 
    PostsPerUser = 1.0*Sum(uqc.PostCount)/(count(Tags))
FROM 
    UserPosts up
INNER JOIN 
    Users u
	   on up.SEUser = u.Id
INNER JOIN 
    Posts p
	   on up.PostId = p.Id
INNER JOIN 
    PostTags pt 
	   on p.Id = pt.PostId
INNER JOIN 
    Tags t 
	   on pt.TagId = t.Id
INNER JOIN 
    UserQuestionCount uqc
	   ON U.Id = uqc.Id
--CROSS APPLY 
--(SELECT (1.0*Sum(uqc.PostCount))/(count(Tags))) CxA(PostsPerUser)
WHERE
    up.RN = 1
GROUP BY 
    t.tagname 
HAVING 
    count(Tags)>30
ORDER BY 
    PostsPerUser DESC

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results