2

Trying to create a query to view the total amount of user's content traffic. That is, how many views the user questions/answers has received, my first attempt has been:

Select Id As [Post Link], CreationDate, Score, ViewCount
From Posts
Where PostTypeId=2 --Answers
    And OwnerUserId=##UserId##
Order By Score desc

When I run this query with my user id all ViewCount columns are NULL, why is that?

PS: This question is related to Query that computes user normalized rank as I believe the user traffic could explain some facts about users popularity and help to create a proper query to measure user performance

Improve this question

1 Answer 1

Reset to default
2

You can't directly view the ViewCount of the answers, based on the ParentId you can link to the Questions and the viewcount of the question is the answer's viewcount.

So the query below will give the ViewCount of the posts you have answered:

SELECT Q.Id AS [Post Link], A.CreationDate, A.Score, Q.ViewCount,
       COALESCE(Q.ViewCount/NULLIF(A.Score, 0), 0) AS Traffic
FROM Posts Q
JOIN Posts A ON A.ParentId = Q.Id
WHERE A.PostTypeId = 2 AND A.OwnerUserId = ##UserId##
ORDER BY A.Score DESC
Improve this answer
7
  • Cool, thx, so... how'd you go about computing the user performance based on traffic & score? That is, sum(score)/ sum(views). The first test cast(ViewCount/Score as float) is giving me Divide by zero error encountered. exception, any idea to prevent this?
    – BPL
    Jun 25, 2018 at 12:45
  • You can use COALESCE(Q.ViewCount/NULLIF(A.Score, 0), 0) to avoid the Divide by zero error
    – Arulkumar
    Jun 25, 2018 at 12:53
  • Great, btw, I guess I've got a typo in my previous comment. As I think the quality factor would be something like "score/ViewCount". Take a look to this https://data.stackexchange.com/stackoverflow/query/866756/user-answers-sorted-by-quality, this one gives results while this other one https://data.stackexchange.com/stackoverflow/query/866803/user-questions-sorted-by-quality does not, why?
    – BPL
    Jun 25, 2018 at 12:58
  • For the Question's Viewcount (PostTypeId = 1), there is no need of the sub query. Your query in the original post will work by changing the PostTypeId = 1. Also I have modified the query in the answer with JOINS, the previous one is not returning the correct data
    – Arulkumar
    Jun 25, 2018 at 13:02
  • Try your query's answer with Jon Skeet's id 22656, any workaround about this?
    – BPL
    Jun 25, 2018 at 13:08
  • Adding TOP will work, like SELECT TOP 30000. Since he answered 34,146 answers in SO, up to SELECT TOP 34100 it doesn't throw any The query processor ran out of internal resources and could not produce a query plan error.
    – Arulkumar
    Jun 25, 2018 at 13:13
  • Ok, cool, your answer provides enough information to continue digging about this topic by myself. Remaining is using the whole information to come up with a good equation that measures a proper user "performance factor". Thx
    – BPL
    Jun 25, 2018 at 13:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .