0

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

edited oct 2 16

created oct 1 16 Nachiket