0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

;with post_rep as
(
  select owneruserid
       , tagname
       , sum(
         case posttypeid 
         when 1 then --Q
           case votetypeid
           when 2 then 5
           when 3 then -2
           else 0
           end
         when 2 then --A
           case votetypeid
           when 1 then 15
           when 2 then 10
           when 3 then -2
           when 9 then bountyamount
           else 0
           end
         else
          0
         end
         ) reputation
  from posts p
  inner join posttags pt on pt.postid = coalesce(p.parentid, p.id)
  inner join tags t on t.id = pt.tagid
  inner join votes v on v.postid = p.id
  where v.votetypeid in (1,2,3,9)
  and owneruserid in (148346,
 213615,
 242871,
 286579,
 290163,
 395857,
 465509,
 499699,
 562769,
 639973,
 678392,
 937153,
 986267,
 1010393,
 1111479,
 1115169,
 1179925,
 1212320,
 1279459,
 1300575,
 1322301,
 1339643,
 1348187,
 1377127,
 1452257,
 1544186,
 1558037,
 1586200,
 1657827,
 1714410,
 1864142,
 1871528,
 1926094,
 2191652,
 2281121,
 2288621,
 2362514,
 2386836,
 2439346,
 2452617,
 2467772,
 2469488,
 2515628,
 2527680,
 2531306,
 2584518,
 2736559,
 2795733,
 2797644,
 2824962,
 2847699,
 2938494,
 3009514,
 3041068,
 3114663,
 3130081,
 3246146,
 3423045,
 3427580,
 3543300,
 3577568,
 3698878,
 3755376,
 3785114,
 3929051,
 3958348,
 3962073,
 4333347,
 4380945,
 4561745,
 4613543,
 4733134,
 4789252,
 4834515,
 4838304,
 4841248,
 4975126,
 5106073,
 5118798,
 5136799,
 5228890,
 5389199,
 5651936,
 5833345,
 5848524,
 5907474,
 6017979,
 6238109,
 6286446,
 6573915,
 6786718,
 7033937,
 7160913)
  group by owneruserid
       , tagname
)
-- Select Id, Reputation from Users where Id in (4333347,213615)

select tagname, [148346],
 [213615],
 [242871],
 [286579],
 [290163],
 [395857],
 [465509],
 [499699],
 [562769],
 [639973],
 [678392],
 [937153],
 [986267],
 [1010393],
 [1111479],
 [1115169],
 [1179925],
 [1212320],
 [1279459],
 [1300575],
 [1322301],
 [1339643],
 [1348187],
 [1377127],
 [1452257],
 [1544186],
 [1558037],
 [1586200],
 [1657827],
 [1714410],
 [1864142],
 [1871528],
 [1926094],
 [2191652],
 [2281121],
 [2288621],
 [2362514],
 [2386836],
 [2439346],
 [2452617],
 [2467772],
 [2469488],
 [2515628],
 [2527680],
 [2531306],
 [2584518],
 [2736559],
 [2795733],
 [2797644],
 [2824962],
 [2847699],
 [2938494],
 [3009514],
 [3041068],
 [3114663],
 [3130081],
 [3246146],
 [3423045],
 [3427580],
 [3543300],
 [3577568],
 [3698878],
 [3755376],
 [3785114],
 [3929051],
 [3958348],
 [3962073],
 [4333347],
 [4380945],
 [4561745],
 [4613543],
 [4733134],
 [4789252],
 [4834515],
 [4838304],
 [4841248],
 [4975126],
 [5106073],
 [5118798],
 [5136799],
 [5228890],
 [5389199],
 [5651936],
 [5833345],
 [5848524],
 [5907474],
 [6017979],
 [6238109],
 [6286446],
 [6573915],
 [6786718],
 [7033937],
 [7160913]
from (
   select *
   from post_rep
) as pvt
PIVOT
(
sum(reputation) for owneruserid in ([148346],
 [213615],
 [242871],
 [286579],
 [290163],
 [395857],
 [465509],
 [499699],
 [562769],
 [639973],
 [678392],
 [937153],
 [986267],
 [1010393],
 [1111479],
 [1115169],
 [1179925],
 [1212320],
 [1279459],
 [1300575],
 [1322301],
 [1339643],
 [1348187],
 [1377127],
 [1452257],
 [1544186],
 [1558037],
 [1586200],
 [1657827],
 [1714410],
 [1864142],
 [1871528],
 [1926094],
 [2191652],
 [2281121],
 [2288621],
 [2362514],
 [2386836],
 [2439346],
 [2452617],
 [2467772],
 [2469488],
 [2515628],
 [2527680],
 [2531306],
 [2584518],
 [2736559],
 [2795733],
 [2797644],
 [2824962],
 [2847699],
 [2938494],
 [3009514],
 [3041068],
 [3114663],
 [3130081],
 [3246146],
 [3423045],
 [3427580],
 [3543300],
 [3577568],
 [3698878],
 [3755376],
 [3785114],
 [3929051],
 [3958348],
 [3962073],
 [4333347],
 [4380945],
 [4561745],
 [4613543],
 [4733134],
 [4789252],
 [4834515],
 [4838304],
 [4841248],
 [4975126],
 [5106073],
 [5118798],
 [5136799],
 [5228890],
 [5389199],
 [5651936],
 [5833345],
 [5848524],
 [5907474],
 [6017979],
 [6238109],
 [6286446],
 [6573915],
 [6786718],
 [7033937],
 [7160913])
) data

fork query

edited nov 6 19 Johirul.Islam

created oct 16 19 rene