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;