0

Please login or register to vote for this query.

(click on this box to dismiss)

regulars per February 2015

Q&A for professional and enthusiast programmers

with c_user as (
select 1711796 as uid -- id:1711796 dukeling (23)
--  ignored 960757 tlama 
union
select 925913 -- id:925913 acheong87 (261)
--  ignored -2 stack-exchange 
union
select 1849664 -- id:1849664 undo (49)
--  ignored 2680216 joshc 
union
select 1476062 -- id:1476062 dennis-meng (13)
union
select 2074608 -- id:2074608 qantas-94-heavy (14)
--  ignored 2024761 r-j 
union
select 2187042 -- id:2187042 richard-tingle (28)
union
select 499214 -- id:499214 jan-dvorak (61)
--  ignored 1252759 jon-clements 
--  ignored 1131435 emracool 
--  ignored 846273 gabriele-petronella 
--  ignored 2993677 ankit-srivastava (19) 
union
select 1401975 -- id:1401975 hamza (389)
--  ignored 2910627 rahim-jaafar 
--  ignored 865172 mohammad-rabi 
union
select 578411 -- id:578411 rene (5437)
union
select 1592648 -- id:1592648 cryptic- (13)
--  ignored 1026459 travis-j 
--  ignored 11343 charlesb 
--  ignored 1292192 kavik 
--  ignored 45974 tom 
--  ignored 508666 peehaa 
--  ignored 1708801 shafik-yaghmour 
union
select 809009 -- id:809009 ondrej-janacek (15)
--  ignored 2016408 leeor 
union
select 2905299 -- id:2905299 godel9 (17)
union
select 1438393 -- id:1438393 amal-murali (33)
--  ignored 727208 teresko 
--  ignored 325521 shiva 
--  ignored 2642204 bartoszkp 
--  ignored 1348195 benjamin-gruenbaum 
--  ignored 2509 dmckee 
--  ignored 293099 athari 
--  ignored 1018966 sandesh 
--  ignored 256196 bohemian 
--  ignored 2822435 marz 
--  ignored 1959948 dalorzo 
union
select 1505120 -- id:1505120 pnuts (67)
--  ignored 486399 hbdgaf 
union
select 2742805 -- id:2742805 oghaza (172)
--  ignored 735868 nirav 
--  ignored 318716 joseph-quinsey 
--  ignored 1792792 phlume 
--  ignored 1072782 dmitry-dubovitsky 
union
select 2581872 -- id:2581872 hichris123 (171)
--  ignored 446554 cory-klein 
--  ignored 565835 darray 
--  ignored 603003 comfreek 
--  ignored 2619939 superscript 
union
select 110707 -- id:110707 wooble (19)
--  ignored 2209007 sumurai8 
union
select 258400 -- id:258400 easwee (622)
union
select 1741671 -- id:1741671 tgmcians (2125)
--  ignored 298282 yasir-arsanukaev 
--  ignored 1928926 emre 
--  ignored 863257 nik-patel 
--  ignored 752976 bartek-banachewicz 
--  ignored 209139 trig 
--  ignored 1623007 joe 
union
select 238886 -- id:238886 wayne-conrad (155)
--  ignored 554340 atticae 
union
select 1043380 -- id:1043380 gunr2171 (2867)
--  ignored 877097 code-magician 
--  ignored 1768232 durron597 
--  ignored 2539335 dark-ashelin 
--  ignored 1029145 nacimota 
--  ignored 3226920 akky777 
--  ignored 360821 abhi 
--  ignored 1639347 cahit-beyaz 
union
select 2371861 -- id:2371861 bjb568 (221)
--  ignored 792066 braiam 
--  ignored 1727263 seth 
union
select 2140173 -- id:2140173 mehow (12)
--  ignored 3212398 dev 
--  ignored 705086 qarma 
--  ignored 610979 frank-schmitt 
--  ignored 27678 andyg 
--  ignored 486233 mmmshuddup 
--  ignored 3512094 dave 
union
select 1149495 -- id:1149495 wouter-j (18)
union
select 1771795 -- id:1771795 omar (133)
--  ignored 1238344 emissary 
--  ignored 2682142 laszlo-papp (292) 
--  ignored 177800 jarrod-roberson 
--  ignored 1003917 shankar-damodaran 
--  ignored 3297613 avinash-raj 
--  ignored 413494 fotanus 
--  ignored 2085149 the-compiler 
--  ignored 1715673 peter-wone 
--  ignored 492336 sashoalm 
--  ignored 2188797 jows 
union
select 1380752 -- id:1380752 codemagic (98)
union
select 3204827 -- id:3204827 howlin (14)
--  ignored 446477 ouflak 
--  ignored 1165866 mustard 
--  ignored 662581 michel-ayres 
--  ignored 1614108 t-v 
--  ignored 2801559 guest271314 
--  ignored 661933 nawfal 
--  ignored 103081 paul 
--  ignored 19405 andrew-medico 
--  ignored 635610 meir 
--  ignored 3442562 user3442562 
--  ignored 774078 rujordan 
--  ignored 1728537 lrineau 
--  ignored 3492388 shweta 
--  ignored 3041194 user0000000 
--  ignored 2846923 the-guy-with-the-hat 
--  ignored 1729349 stanislav 
--  ignored 2017294 sma-rahman 
--  ignored 811 shog9 
union
select 400617 -- id:400617 davidism (111)
union
select 1811992 -- id:1811992 web-tiki (29)
--  ignored 1019630 neeku (76) 
--  ignored 790387 burhan-khalid 
union
select 12892 -- id:12892 cristian-ciupitu (11)
--  ignored 2587867 asr (24) 
union
select 2664200 -- id:2664200 springlearner (39)
union
select 2619912 -- id:2619912 programfox (577)
--  ignored 456814 cupcake 
--  ignored 3136339 nicolas-r 
--  ignored 2446155 santa-claus 
union
select 3622940 -- id:3622940 unihedron (605)
--  ignored 455582 twelfth 
--  ignored 1401094 patsy-issa 
--  ignored 3865158 igor 
--  ignored 381149 kayasax 
--  ignored 3029904 user23 
--  ignored 1172451 dan 
--  ignored 522444 hovercraft-full-of-eels 
--  ignored 2387772 yuvi 
union
select 2246344 -- id:2246344 sam (1082)
--  ignored 247702 stijn 
union
select 119775 -- id:119775 jean-francois-corbett (30)
--  ignored 3761928 shell 
union
select 91696 -- id:91696 albireo (56)
--  ignored 1652962 cimmanon 
--  ignored 114251 arun-p-johny 
--  ignored 1332892 strider 
--  ignored 3928176 arif-suhail-123 
--  ignored 847338 daniel-amaya 
--  ignored 3909875 its-justjack (26) 
union
select 731947 -- id:731947 cs (27)
--  ignored 1899424 dcorking 
--  ignored 2850543 millie-smith 
--  ignored 851498 florian-margaine 
--  ignored 855760 darkyen00 
--  ignored 3755509 jazib-bashir 
--  ignored 2677179 sport (13) 
--  ignored 426671 bluefeet 
--  ignored 3607246 maddy 
union
select 1699210 -- id:1699210 bummi (22)
union
select 1864610 -- id:1864610 mike-w (20)
--  ignored 2407212 jonathan 
--  ignored 3522687 paulalexandru 
--  ignored 771848 alecxe 
union
select 1816580 -- id:1816580 artjom-b (32)
--  ignored 1761003 mave- 
--  ignored 3395049 rahul-khosla 
--  ignored 3933332 rizier123 
--  ignored 3005188 ffisegydd 
--  ignored 1104384 midhun-mp 
--  ignored 2135853 manthan 
union
select 1188035 -- id:1188035 sjagr (13)
--  ignored 1999155 kaushik 
--  ignored 3277047 13375 
--  ignored 494094 hattenn 
union
select 3198973 -- id:3198973 rubberduck (26)
--  ignored 3477950 the-paramagnetic-croissant 
union
select 656243 -- id:656243 lynn-crumbling (87)
--  ignored 2763709 the-dictator 
--  ignored 4424245 not-gunr2171 (833) 
--  ignored 1135714 cecexx (22) 
--  ignored 4241535 chrismas007 
--  ignored 2065702 zach-saucier 
--  ignored 4044988 megabytes 
union
select 839601 -- id:839601 gnat (38)
union
select 4015623 -- id:4015623 alex-k (46)
union
select 2313887 -- id:2313887 neil-lunn (17)
union
select 359284 -- id:359284 kevin-brown (62)
--  ignored 1477667 dfeuer 
--  ignored 1140748 alain-janinm 
--  ignored 1228 will 
--  ignored 927408 jurgemaister 
union
select 758446 -- id:758446 blackvegetable (23)
--  ignored 2043621 okuma-tony 
--  ignored 1466640 akholland 
--  ignored 4458531 simone 
--  ignored 560648 lightness-races-in-orbit 
--  ignored 211627 jdb 
union
select 1014938 -- id:1014938 zero-piraeus (49)
union
select 2756409 -- id:2756409 tylerh (135)
union
select 3234422 -- id:3234422 functionr (14)
--  ignored 4461161 computer-one 
--  ignored 2792080 code-geek 
--  ignored 2422013 yekuomreqy (30) 
--  ignored 359307 kurt-pfeifle 
--  ignored 3761297 committedandroider 
--  ignored 203262 shantanu-gupta 
--  ignored 934239 xan 
--  ignored 692942 lankymart 
--  ignored 14768 joshua 
),
tot_closed as (
select count(*) [closed]
from posthistory
where postHistoryTypeId = 10
and creationdate > '2013-11-19' -- room started
) ,
all_closed_posts as
(
select postid
     , title
     , cv.id userid
from posthistory ph
inner join posts p 
   on p.id = ph.postid
cross apply openjson(ph.text, '$.Voters') 
 with (Id int '$.Id' ) cv
inner join c_user 
   on cv.id = uid
where ph.PostHistoryTypeId = 10
and ph.creationdate > '2013-11-19' -- room started
)

select 
  (select closed from tot_closed) [total closed]
, (select count(distinct postid) from all_closed_posts ) [total closed]
, (select count(distinct postid) from 
   (select postid 
    from all_closed_posts 
    group by postid 
    having count(*) = 1 ) data
    ) [1 user closed]
, (select count(distinct postid) from 
   (select postid 
    from all_closed_posts 
    group by postid 
    having count(*) = 2 ) data
    ) [2 users closed]
, (select count(distinct postid) from 
   (select postid 
    from all_closed_posts 
    group by postid 
    having count(*) = 3 ) data
    ) [3 users closed]
, (select count(distinct postid) from 
   (select postid 
    from all_closed_posts 
    group by postid 
    having count(*) = 4 ) data
    ) [4 users closed]
, (select count(distinct postid) from 
   (select postid 
    from all_closed_posts 
    group by postid 
    having count(*) = 5 ) data
    ) [5 users closed]

/*
select concat('site://q/',postid,'|', title) as [Closed Link]
     , count(*)
from posthistory ph
inner join posts p 
   on p.id = ph.postid
cross apply openjson(ph.text, '$.Voters') 
 with (Id int '$.Id' ) cv
inner join c_user 
   on cv.id = uid
where ph.PostHistoryTypeId = 10
and ph.creationdate > '2013-11-19' -- room started
group by ph.postid, title
order by count(*) desc
*/

fork query

edited jun 29

created jun 29 rene