0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for academics and those enrolled in higher education

-- Question 5
 -- As a proxy for engagement, use the Posts table to query for 
 -- the post with the fifth highest Score since the website went live.  
 -- (For you non-TSQL users, year(field_name) is the syntax to retrieve 
 -- the year datepart)
 
 WITH ranked_posts AS (
   SELECT 
     TOP 100 *,
     RANK() OVER (ORDER BY Score DESC) rnk

   FROM Posts p 
   ORDER BY Score DESC
)
SELECT * FROM ranked_posts WHERE rnk=5;



-- Question 4
-- Using the Votes, VoteTypes, Posts, and PostTypes tables, 
-- display the users who have the highest upvote to post ratio 
-- of all time for posts of type Question.  i.e. Which users on 
-- the platform ask the most insightful questions as judged by their 
-- peers with accounts?
/*
SELECT
  TOP 100
  p.OwnerUserId,
  pt.Name post_type,
  SUM(CASE WHEN vt.Name='UpMod' THEN 1 ELSE 0 END) num_up_votes,
  COUNT(DISTINCT(PostId)) num_posts,
  (CAST(SUM(CASE WHEN vt.Name='UpMod' THEN 1 ELSE 0 END) AS float)/COUNT(DISTINCT(PostId))) votes_post_ratio

FROM Votes v
JOIN Posts p ON v.PostId = p.Id
LEFT JOIN PostTypes pt ON pt.Id = p.PostTypeId
LEFT JOIN VoteTypes vt ON vt.Id = v.VoteTypeId

WHERE pt.Name = 'Question'
GROUP BY
  p.OwnerUserId,
  pt.Name 
  
ORDER BY 
 votes_post_ratio DESC;
*/ 
-- COMMENT: Users with fewest posts have most upvotes/highest ratio




-- Question 3
-- Using the Posts and PostType tables, find the average number of 
-- Favorites per post for each post type.  Once again, 
-- please note something interesting this query tells you about the 
-- database below your code.
/*
SELECT 

  pt.Id post_type_id, 
  pt.Name post_type_name,
  AVG(CAST(p.FavoriteCount as float)) avg_fvt,
  -- Sanity checks below here
  COUNT(*) my_count, 
  SUM(CASE WHEN p.FavoriteCount >= 0 THEN 1 ELSE 0 END) count_of_nums,
  SUM(p.FavoriteCount) sum_fvt,
  
FROM Posts p LEFT JOIN PostTypes pt ON p.PostTypeId = pt.Id
GROUP BY pt.Id, pt.Name ORDER BY 1 DESC
;
*/
-- COMMENT: only post type=1 have favorite scores at all.  The Avg value does not include nulls.


-- Question 2
-- Using the Users table, please query the 25 most frequent 
-- “Reputation” values users have on the site.  
--Please comment below your code something interesting this 
-- result tells you about the “Reputation” field.
--SELECT TOP 25 COUNT(*), Reputation FROM Users GROUP BY Reputation ORDER BY 1 DESC;
-- COMMENT: The most commonly occuring reputation scores have 1s and 0s only?


-- Question 1
--SELECT COUNT(DISTINCT(OwnerUserId)) FROM Posts;
--SELECT COUNT(DISTINCT(OwnerUserId)) FROM Posts WHERE OwnerUserId >=0;

fork query

edited may 18 22

created may 17 22 anonymous