1

Please login or register to vote for this query.

(click on this box to dismiss)

An estimated count, from who got you how many downs. BEWARE! If someone votes all your posts silently down, this query won't see him. If someone actually SUPPORTS you, this query will show him as your downvoter. So is it - this is the best I have. DO NOT QUARREL SOMEONE BASED ON THIS QUERY!

Q&A about the site for people who use Blender to create 3D graphics, animations, or games

DECLARE @UserId INT;

SELECT @UserId = Id FROM Users WHERE AccountId = ##SEAccountId:int##;

WITH PostRaw AS (
  SELECT
    ParentId AS QId,
    Id AS PId
  FROM Posts
  WHERE
    PostTypeId = 2
    AND
    OwnerUserId = @UserId
  
  UNION

  SELECT
    Id AS QId,
    Id AS PId
  FROM Posts
  WHERE
    PostTypeId = 1
    AND
    OwnerUserId = @UserId
),
PostsByQ AS (
  SELECT
    QId,
    COUNT(*) AS Count
  FROM PostRaw
  GROUP BY QId
),
DVByQRaw AS (
  SELECT
    Posts.Id AS QId,
    COUNT(*) AS Count
  FROM Posts, Votes
  WHERE
    Posts.Id = Votes.PostId
    AND
    Posts.PostTypeId = 1
    AND
    Votes.VoteTypeId = 3
    AND
    Posts.OwnerUserId = @UserId
  GROUP BY Posts.Id

  UNION
  
  SELECT
    Posts.ParentId AS QId,
    COUNT(*) AS Count
  FROM Posts, Votes
  WHERE
    Posts.Id = Votes.PostId
    AND
    Posts.PostTypeId = 2
    AND
    Votes.VoteTypeId = 3
    AND
    Posts.OwnerUserId = @UserId
  GROUP BY Posts.ParentId
),
DVByQ AS (
  SELECT
    QId,
    SUM(Count) AS Count
  FROM DVByQRaw
  GROUP BY QId
),
ActuRaw AS (
  SELECT
    Id AS QId,
    OwnerUserId AS UserId
  FROM Posts
  WHERE
    Posts.Id IN (SELECT QId FROM PostsByQ)
  
  UNION
  
  SELECT
    ParentId AS QId,
    OwnerUserId AS UserId
  FROM Posts
  WHERE
    Posts.ParentId IN (SELECT QId FROM PostsByQ)
  
  UNION
  
  SELECT
    PostId AS QId,
    UserId
  FROM Comments
  WHERE
    Id IN (SELECT QId FROM PostsByQ)
  
  UNION
  
  SELECT
    Posts.ParentId AS QId,
    Comments.UserId
  FROM Comments, Posts
  WHERE
    Comments.PostId = Posts.Id
    AND
    Posts.ParentId IN (SELECT QId FROM PostsByQ)
),
Actu AS (
  SELECT QId, UserId
  FROM ActuRaw
  WHERE
    UserId <> @UserId
    AND
    UserId IS NOT NULL
  GROUP BY QId, UserId
),
ActCount AS (
  SELECT
    QId,
    COUNT(*) AS Count
  FROM Actu
  GROUP BY QId
)
SELECT
  Actu.UserId AS [User Link],
  SUM(
    CASE
      WHEN 1.0 * DVByQ.Count / ActCount.Count > PostsByQ.Count
      THEN PostsByQ.Count
      ELSE 1.0 * DVByQ.Count / ActCount.Count
    END
  ) AS EstDV
FROM PostsByQ, DVByQ, Actu, ActCount
WHERE
  PostsByQ.QId = DVByQ.QId
  AND
  PostsByQ.QId = ActCount.QId
  AND
  PostsByQ.QId = Actu.QId
GROUP BY Actu.UserId
ORDER BY EstDV DESC, Actu.UserId

fork query

edited feb 19 20

created feb 19 20 peterh - PLEASE DELETE THIS ACCOUNT