Preguntas y respuestas sobre el sitio para programadores y profesionales de la informática
-- 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)
-- Version 1 - with no date
RANK() OVER (ORDER BY Score DESC) rnk,
FROM Posts p
-- Version 2 - 5th ranked post by year
WITH ranked_posts AS (
RANK() OVER (partition by year(CreationDate) ORDER BY Score DESC) rnk,
FROM Posts p
--ORDER BY post_yr, Score DESC
SELECT * FROM ranked_posts WHERE rnk=5 ORDER BY post_year;
-- 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?
SUM(CASE WHEN vt.Name='UpMod' THEN 1 ELSE 0 END) num_up_votes,
(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'
-- 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.
AVG(CAST(p.FavoriteCount as float)) avg_fvt,
-- Sanity checks below here
SUM(CASE WHEN p.FavoriteCount >= 0 THEN 1 ELSE 0 END) count_of_nums,
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;