0

Please login or register to vote for this query.

(click on this box to dismiss)

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

;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 (  22996,
 67405,
 133374,
 179081,
 217802,
 245549,
 279858,
 283296,
 380038,
 395857,
 417357,
 419338,
 470184,
 497600,
 579980,
 643014,
 658493,
 681911,
 684979,
 700023,
 722263,
 741099,
 797880,
 824624,
 826983,
 847663,
 853462,
 854585,
 865662,
 868736,
 869402,
 996366,
 1010393,
 1020302,
 1085946,
 1099534,
 1105571,
 1150636,
 1245262,
 1253251,
 1256369,
 1293964,
 1311255,
 1357690,
 1364327,
 1367788,
 1452257,
 1461210,
 1474615,
 1505986,
 1620746,
 1658908,
 1663093,
 1696653,
 1714410,
 1724926,
 1754197,
 1756010,
 1775763,
 1870498,
 1908423,
 1935415,
 1990514,
 2040628,
 2051572,
 2093984,
 2112259,
 2147347,
 2168406,
 2220487,
 2255757,
 2280645,
 2385818,
 2394319,
 2444293,
 2462245,
 2480410,
 2515942,
 2527680,
 2546106,
 2568137,
 2637212,
 2663583,
 2680707,
 2728105,
 2751512,
 2824962,
 2838606,
 2876799,
 2902280,
 2945737,
 2991243,
 3041068,
 3054726,
 3198732,
 3282130,
 3335387,
 3487835,
 3497273,
 3501476,
 3568242,
 3633343,
 3670532,
 3681744,
 3704652,
 3747801,
 3784367,
 3849791,
 3979919,
 3990607,
 4013571,
 4013781,
 4058635,
 4068755,
 4092486,
 4373828,
 4380945,
 4386560,
 4398908,
 4476617,
 4497662,
 4583366,
 4620420,
 4622951,
 4652515,
 4652819,
 4720623,
 4802797,
 4805219,
 4842101,
 4937674,
 4969461,
 4987305,
 5000294,
 5016984,
 5059640,
 5107084,
 5121662,
 5130560,
 5165960,
 5197007,
 5257450,
 5307788,
 5310324,
 5390189,
 5404771,
 5435514,
 5513231,
 5605919,
 5641188,
 5643899,
 5737155,
 5738942,
 5743949,
 5766416,
 5848524,
 5931447,
 5979241,
 6056683,
 6140421,
 6225687,
 6417704,
 6485667,
 6555213,
 6719992,
 6740140,
 6930321,
 7144734,
 7295598)
  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, [22996],
 [67405],
 [133374],
 [179081],
 [217802],
 [245549],
 [279858],
 [283296],
 [380038],
 [395857],
 [417357],
 [419338],
 [470184],
 [497600],
 [579980],
 [643014],
 [658493],
 [681911],
 [684979],
 [700023],
 [722263],
 [741099],
 [797880],
 [824624],
 [826983],
 [847663],
 [853462],
 [854585],
 [865662],
 [868736],
 [869402],
 [996366],
 [1010393],
 [1020302],
 [1085946],
 [1099534],
 [1105571],
 [1150636],
 [1245262],
 [1253251],
 [1256369],
 [1293964],
 [1311255],
 [1357690],
 [1364327],
 [1367788],
 [1452257],
 [1461210],
 [1474615],
 [1505986],
 [1620746],
 [1658908],
 [1663093],
 [1696653],
 [1714410],
 [1724926],
 [1754197],
 [1756010],
 [1775763],
 [1870498],
 [1908423],
 [1935415],
 [1990514],
 [2040628],
 [2051572],
 [2093984],
 [2112259],
 [2147347],
 [2168406],
 [2220487],
 [2255757],
 [2280645],
 [2385818],
 [2394319],
 [2444293],
 [2462245],
 [2480410],
 [2515942],
 [2527680],
 [2546106],
 [2568137],
 [2637212],
 [2663583],
 [2680707],
 [2728105],
 [2751512],
 [2824962],
 [2838606],
 [2876799],
 [2902280],
 [2945737],
 [2991243],
 [3041068],
 [3054726],
 [3198732],
 [3282130],
 [3335387],
 [3487835],
 [3497273],
 [3501476],
 [3568242],
 [3633343],
 [3670532],
 [3681744],
 [3704652],
 [3747801],
 [3784367],
 [3849791],
 [3979919],
 [3990607],
 [4013571],
 [4013781],
 [4058635],
 [4068755],
 [4092486],
 [4373828],
 [4380945],
 [4386560],
 [4398908],
 [4476617],
 [4497662],
 [4583366],
 [4620420],
 [4622951],
 [4652515],
 [4652819],
 [4720623],
 [4802797],
 [4805219],
 [4842101],
 [4937674],
 [4969461],
 [4987305],
 [5000294],
 [5016984],
 [5059640],
 [5107084],
 [5121662],
 [5130560],
 [5165960],
 [5197007],
 [5257450],
 [5307788],
 [5310324],
 [5390189],
 [5404771],
 [5435514],
 [5513231],
 [5605919],
 [5641188],
 [5643899],
 [5737155],
 [5738942],
 [5743949],
 [5766416],
 [5848524],
 [5931447],
 [5979241],
 [6056683],
 [6140421],
 [6225687],
 [6417704],
 [6485667],
 [6555213],
 [6719992],
 [6740140],
 [6930321],
 [7144734],
 [7295598]
from (
   select *
   from post_rep
) as pvt
PIVOT
(
sum(reputation) for owneruserid in ([22996],
 [67405],
 [133374],
 [179081],
 [217802],
 [245549],
 [279858],
 [283296],
 [380038],
 [395857],
 [417357],
 [419338],
 [470184],
 [497600],
 [579980],
 [643014],
 [658493],
 [681911],
 [684979],
 [700023],
 [722263],
 [741099],
 [797880],
 [824624],
 [826983],
 [847663],
 [853462],
 [854585],
 [865662],
 [868736],
 [869402],
 [996366],
 [1010393],
 [1020302],
 [1085946],
 [1099534],
 [1105571],
 [1150636],
 [1245262],
 [1253251],
 [1256369],
 [1293964],
 [1311255],
 [1357690],
 [1364327],
 [1367788],
 [1452257],
 [1461210],
 [1474615],
 [1505986],
 [1620746],
 [1658908],
 [1663093],
 [1696653],
 [1714410],
 [1724926],
 [1754197],
 [1756010],
 [1775763],
 [1870498],
 [1908423],
 [1935415],
 [1990514],
 [2040628],
 [2051572],
 [2093984],
 [2112259],
 [2147347],
 [2168406],
 [2220487],
 [2255757],
 [2280645],
 [2385818],
 [2394319],
 [2444293],
 [2462245],
 [2480410],
 [2515942],
 [2527680],
 [2546106],
 [2568137],
 [2637212],
 [2663583],
 [2680707],
 [2728105],
 [2751512],
 [2824962],
 [2838606],
 [2876799],
 [2902280],
 [2945737],
 [2991243],
 [3041068],
 [3054726],
 [3198732],
 [3282130],
 [3335387],
 [3487835],
 [3497273],
 [3501476],
 [3568242],
 [3633343],
 [3670532],
 [3681744],
 [3704652],
 [3747801],
 [3784367],
 [3849791],
 [3979919],
 [3990607],
 [4013571],
 [4013781],
 [4058635],
 [4068755],
 [4092486],
 [4373828],
 [4380945],
 [4386560],
 [4398908],
 [4476617],
 [4497662],
 [4583366],
 [4620420],
 [4622951],
 [4652515],
 [4652819],
 [4720623],
 [4802797],
 [4805219],
 [4842101],
 [4937674],
 [4969461],
 [4987305],
 [5000294],
 [5016984],
 [5059640],
 [5107084],
 [5121662],
 [5130560],
 [5165960],
 [5197007],
 [5257450],
 [5307788],
 [5310324],
 [5390189],
 [5404771],
 [5435514],
 [5513231],
 [5605919],
 [5641188],
 [5643899],
 [5737155],
 [5738942],
 [5743949],
 [5766416],
 [5848524],
 [5931447],
 [5979241],
 [6056683],
 [6140421],
 [6225687],
 [6417704],
 [6485667],
 [6555213],
 [6719992],
 [6740140],
 [6930321],
 [7144734],
 [7295598])
) data

fork query

edited nov 7 19 anonymous

created oct 16 19 rene