Get Reputation of User

0

Please login or register to vote for this query.

(click on this box to dismiss)

スタック・オーバーフローMeta

プログラマーと熱狂的なプログラマーのためのサイトに関する質問と回答

;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 (  7648,
 64967,
 112976,
 179372,
 201601,
 418293,
 495815,
 524283,
 698067,
 807231,
 845800,
 1033422,
 1034526,
 1130933,
 1165907,
 1227229,
 1245418,
 1281433,
 1290485,
 1301102,
 1374804,
 1395179,
 1518003,
 1553154,
 1560062,
 1620177,
 1631205,
 1632556,
 1689706,
 1743555,
 1757622,
 1791279,
 1884063,
 1926931,
 1995840,
 2106858,
 2107415,
 2127383,
 2335990,
 2376945,
 2381737,
 2399935,
 2411320,
 2447860,
 2525128,
 2587904,
 2610694,
 2615065,
 2769240,
 2772562,
 2835778,
 2878934,
 2908422,
 2995163,
 3109227,
 3226997,
 3387134,
 3415409,
 3605801,
 3638373,
 3727471,
 3789843,
 3803714,
 3985612,
 3993270,
 4020395,
 4084039,
 4198584,
 4203061,
 4205935,
 4288229,
 4334382,
 4368392,
 4373061,
 4373226,
 4567967,
 4671077,
 4684766,
 4738776,
 4862230,
 4898764,
 4960592,
 5009666,
 5115981,
 5133387,
 5154433,
 5235668,
 5278178,
 5293399,
 5350045,
 5428929,
 5429527,
 5451958,
 5481576,
 5482891,
 5494467,
 5583558,
 5698888,
 5783379,
 6042404,
 6049788,
 6090280,
 6412706,
 6454030,
 6471140,
 6786353,
 6803890,
 7010746,
 7168247,
 7485867,
 8309650)
  and (tagname like '%theano%' 
  or tagname like '%tensorflow%' 
  or tagname like '%scikit-learn%' 
  or tagname like '%caffe%' 
  or  tagname like '%keras%' 
  or  tagname like '%mahout%'
  or tagname like '%apache-spark-mllib%'
  or tagname like '%h2o%'
  or tagname like '%torch%'
  or tagname like '%weka%'
  )
  group by owneruserid
       , tagname
)
-- Select Id, Reputation from Users where Id in (4333347,213615)

select tagname, [7648],
 [64967],
 [112976],
 [179372],
 [201601],
 [418293],
 [495815],
 [524283],
 [698067],
 [807231],
 [845800],
 [1033422],
 [1034526],
 [1130933],
 [1165907],
 [1227229],
 [1245418],
 [1281433],
 [1290485],
 [1301102],
 [1374804],
 [1395179],
 [1518003],
 [1553154],
 [1560062],
 [1620177],
 [1631205],
 [1632556],
 [1689706],
 [1743555],
 [1757622],
 [1791279],
 [1884063],
 [1926931],
 [1995840],
 [2106858],
 [2107415],
 [2127383],
 [2335990],
 [2376945],
 [2381737],
 [2399935],
 [2411320],
 [2447860],
 [2525128],
 [2587904],
 [2610694],
 [2615065],
 [2769240],
 [2772562],
 [2835778],
 [2878934],
 [2908422],
 [2995163],
 [3109227],
 [3226997],
 [3387134],
 [3415409],
 [3605801],
 [3638373],
 [3727471],
 [3789843],
 [3803714],
 [3985612],
 [3993270],
 [4020395],
 [4084039],
 [4198584],
 [4203061],
 [4205935],
 [4288229],
 [4334382],
 [4368392],
 [4373061],
 [4373226],
 [4567967],
 [4671077],
 [4684766],
 [4738776],
 [4862230],
 [4898764],
 [4960592],
 [5009666],
 [5115981],
 [5133387],
 [5154433],
 [5235668],
 [5278178],
 [5293399],
 [5350045],
 [5428929],
 [5429527],
 [5451958],
 [5481576],
 [5482891],
 [5494467],
 [5583558],
 [5698888],
 [5783379],
 [6042404],
 [6049788],
 [6090280],
 [6412706],
 [6454030],
 [6471140],
 [6786353],
 [6803890],
 [7010746],
 [7168247],
 [7485867],
 [8309650]
from (
   select *
   from post_rep
) as pvt
PIVOT
(
sum(reputation) for owneruserid in ([7648],
 [64967],
 [112976],
 [179372],
 [201601],
 [418293],
 [495815],
 [524283],
 [698067],
 [807231],
 [845800],
 [1033422],
 [1034526],
 [1130933],
 [1165907],
 [1227229],
 [1245418],
 [1281433],
 [1290485],
 [1301102],
 [1374804],
 [1395179],
 [1518003],
 [1553154],
 [1560062],
 [1620177],
 [1631205],
 [1632556],
 [1689706],
 [1743555],
 [1757622],
 [1791279],
 [1884063],
 [1926931],
 [1995840],
 [2106858],
 [2107415],
 [2127383],
 [2335990],
 [2376945],
 [2381737],
 [2399935],
 [2411320],
 [2447860],
 [2525128],
 [2587904],
 [2610694],
 [2615065],
 [2769240],
 [2772562],
 [2835778],
 [2878934],
 [2908422],
 [2995163],
 [3109227],
 [3226997],
 [3387134],
 [3415409],
 [3605801],
 [3638373],
 [3727471],
 [3789843],
 [3803714],
 [3985612],
 [3993270],
 [4020395],
 [4084039],
 [4198584],
 [4203061],
 [4205935],
 [4288229],
 [4334382],
 [4368392],
 [4373061],
 [4373226],
 [4567967],
 [4671077],
 [4684766],
 [4738776],
 [4862230],
 [4898764],
 [4960592],
 [5009666],
 [5115981],
 [5133387],
 [5154433],
 [5235668],
 [5278178],
 [5293399],
 [5350045],
 [5428929],
 [5429527],
 [5451958],
 [5481576],
 [5482891],
 [5494467],
 [5583558],
 [5698888],
 [5783379],
 [6042404],
 [6049788],
 [6090280],
 [6412706],
 [6454030],
 [6471140],
 [6786353],
 [6803890],
 [7010746],
 [7168247],
 [7485867],
 [8309650])
) data

Enter Parameters

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