Q&A for amateur radio enthusiasts
-- Tables used: --posts: id, tags --users: id, reputation, --badges: id, userid, tagbased(tru), class(1:Gold), name(tagname) -- posttags: postid, tagid --Tags: id, tagname --------------------------------------query1--------------------------------------- --select name,count(*) from badges where class = 1 and tagbased ='true' and --name in (select top(10) tagname from tags order by count desc) --group by name --Output --android195c#469c++335html107ios114java424javascript334jquery199php206python314 --Although java script has maximum number of posts it does not has maximum -- distinct number of gold badge winners -- 1) Less number of people are dominating the field ------------------------------------------------------------------------------------ -------------------------------------query2---------------------------------------- -- Do they have gold badge in any other tag? --select count(distinct userid) from badges where class = 1 and tagbased ='true' and --name <> 'javascript' and userid in ( --select distinct(userid) from badges where class = 1 and tagbased ='true' and --name = 'javascript') -- Output 252 -- So out of 332 Gold badge users of javascript 252 have a gold badge in another tag as well WOW! -------------------------------------query3--------------------------------------- /*select displayname , reputation, COUNT(CASE WHEN class = 1 THEN 1 END) AS Gold , COUNT(CASE WHEN class = 2 THEN 1 END) AS Silver , COUNT(CASE WHEN class = 3 THEN 1 END) AS Bronze , COUNT(class) AS Total from badges join users on Users.Id = Badges.UserId where tagbased = 'true' and userid in( select distinct(userid) from badges where class = 1 and tagbased ='true' and name = 'javascript') group by displayname, reputation order by Gold desc ,Silver desc ,Bronze desc ,displayname desc -- OMG Jon Skeet does not has a javascript gold! */ ---------------------------------- query4 ------------------------------------------ -- Users with only 1 gold badge how willing are they to explore other areas /* select name, userid from badges where tagbased = 'true' and class = 1 and userid in ( select userid from badges join users on Users.Id = Badges.UserId where tagbased = 'true' group by userid having COUNT(CASE WHEN class = 1 THEN 1 END) = 1 ) */ ----------------------------------- query 5 ---------------------------------------- --How to calculate number of answers for a particular tag for a user /* select t.TagName, count(t.Id) as AnswersForTag, sum(a.score) as score from Posts as q inner join PostTags as pt on pt.PostId = q.Id inner join Tags as t on t.Id = pt.TagId inner join Posts as a on a.ParentId = q.Id where tagname = 'ocaml' and a.OwnerUserId = 298143 --ocaml298143 group by t.TagName having count(t.Id) >= 5 order by 2 desc */ ---------------------------------- query 6 --------------------------------------- /* select count(t.Id) as AnswersForTag, sum(a.score) as score from Posts as q inner join PostTags as pt on pt.PostId = q.Id inner join Tags as t on t.Id = pt.TagId inner join Posts as a on a.ParentId = q.Id where a.OwnerUserId = 298143 --ocaml298143 having count(t.Id) >= 5 order by 2 desc */ --------------------------------- query 7 -------------------------------------- /* -- Gold Tag posts and score select table1.* from (select t.TagName, a.OwnerUserId, count(t.Id) as AnswersForTag, sum(a.score) as Tagscore from Posts as q inner join PostTags as pt on pt.PostId = q.Id inner join Tags as t on t.Id = pt.TagId inner join Posts as a on a.ParentId = q.Id group by t.TagName,a.OwnerUserId having count(t.Id) >= 5 ) table1 inner join (select name, userid from badges where tagbased = 'true' and class = 1 and userid in ( select userid from badges join users on Users.Id = Badges.UserId where tagbased = 'true' group by userid having COUNT(CASE WHEN class = 1 THEN 1 END) = 1 ) )table2 on table1.tagname = table2.name and table1.owneruserid = table2.userid order by table1.owneruserid */ --------------------------------------- query 8 --------------------------------- -- Total answers of users /* select table1.* from (select a.OwnerUserId, count(t.Id) as Answers, sum(a.score) as score from Posts as q inner join PostTags as pt on pt.PostId = q.Id inner join Tags as t on t.Id = pt.TagId inner join Posts as a on a.ParentId = q.Id group by a.OwnerUserId having count(t.Id) >= 5 ) table1 inner join (select userid from badges where tagbased = 'true' and class = 1 and userid in ( select userid from badges join users on Users.Id = Badges.UserId where tagbased = 'true' group by userid having COUNT(CASE WHEN class = 1 THEN 1 END) = 1 ) )table2 on table1.owneruserid = table2.userid order by table1.owneruserid */ -------------------------------- query 9 ------------------------------------------- --Now let us analyze these users reputation due to their core expertise and due to other tags ----------------------------------- query10 ------------------------------------- select table1.* from (SELECT p.OwnerUserId UserId, SUM( CASE WHEN v.VoteTypeId = 1 THEN 15 WHEN v.VoteTypeId = 2 AND p.PostTypeId = 1 THEN 5 WHEN v.VoteTypeId = 2 AND p.PostTypeId = 2 THEN 10 WHEN v.VoteTypeId = 3 THEN -2 ELSE 0 END) AReputation, tagname FROM Votes v INNER JOIN POSTS p ON p.Id= v.PostId INNER JOIN users u ON u.id= p.owneruserid INNER JOIN posttags pt ON pt.postid = p.id INNER JOIN tags t ON pt.tagid = t.id WHERE p.posttypeid IN (1,2) GROUP BY p.OwnerUserId,tagname --ORDER BY p.OwnerUserId,tagname )table1 inner join (select name, userid from badges where tagbased = 'true' and class = 1 and userid in ( select userid from badges join users on Users.Id = Badges.UserId where tagbased = 'true' group by userid having COUNT(CASE WHEN class = 1 THEN 1 END) = 1 ) )table2 on table1.tagname = table2.name and table1.UserId = table2.userid order by table1.UserId