Q&A for peer programmer code reviews
--section 8- 8. Find the posts which were “active” for longer than 100 days --(a post is “active” in the time period after its creation and before it is being --closed). For each user who owns at least one post as mentioned above, --display the posts which received the top 3 scores. --solution- --with ranked_score_per_user as (select row_number() over( -- partition by OwnerUserId -- order by Score desc) as 'post_score_rank', --Id,OwnerUserId,CreationDate,ClosedDate,score from posts --where OwnerUserId in ( -- select distinct top (100) OwnerUserId from posts -- where ClosedDate-CreationDate > 100 or ClosedDate is null)) --select * from ranked_score_per_user --where post_score_rank <= 3; --section 9- 9. Find users who posted 2 posts from the same type in a time period --which is smaller than 30 days. Refer only to users who has a positive value --as user_id. For each of the 2 consecutive posts display the post_id and the --creation date. In addition, display the id of the user who created the posts --and the time difference between the creations of the 2 posts. --solution- --part 1- --with organized_table as (select Id,OwnerUserId,PostTypeId,CreationDate, --lag(CreationDate,1,CreationDate-50) over ( --partition by OwnerUserId,PostTypeId --order by CreationDate) as 'Prev_post_type_creation_date', --datediff(day,lag(CreationDate,1,CreationDate-50) over ( --partition by OwnerUserId,PostTypeId --order by CreationDate),CreationDate) as 'Diff_from_prev_post_type', --datediff(day,CreationDate,lead(CreationDate,1,CreationDate+50) over ( --partition by OwnerUserId,PostTypeId --order by CreationDate)) as 'Diff_from_next_post_type' --from posts --where OwnerUserId between 1 and 50) --select Id,OwnerUserId,PostTypeId,CreationDate from organized_table --where Diff_from_prev_post_type < 30 or Diff_from_next_post_type < 30; --part 2 (in addition)- --with organized_table as (select Id,OwnerUserId,PostTypeId,CreationDate, --lag(CreationDate,1,CreationDate-50) over ( --partition by OwnerUserId,PostTypeId --order by CreationDate) as 'Prev_post_type_creation_date', --datediff(day,lag(CreationDate,1,CreationDate-50) over ( --partition by OwnerUserId,PostTypeId --order by CreationDate),CreationDate) as 'Diff_from_prev_post_type', --datediff(day,CreationDate,lead(CreationDate,1,CreationDate+50) over ( --partition by OwnerUserId,PostTypeId --order by CreationDate)) as 'Diff_from_next_post_type' --from posts --where OwnerUserId between 1 and 50) --select OwnerUserId,PostTypeId,Diff_from_prev_post_type from organized_table --where Diff_from_prev_post_type < 30; --section 10- 10. Answer the following: --a. Use a temporary table in order to save additional details regarding each --user’s creation date in the system. For each user, save the user’s id, --his display name and creation date. In addition, extract each part --(day, month, year) of the creation date, and the last day in the creation month. --b. Use the temporary table you created and find how many users joined the --system in each month (pay attention: June 2016 is defined as different month --than June 2017). --solution- --with post_table as ( --select Id,DisplayName,CreationDate,year(CreationDate) as 'Creation_date_year', --month(CreationDate) as 'Creation_date_month',day(CreationDate) as 'Creation_date_day', --eomonth(cast(CreationDate as date)) as 'last_day_of_month_creation_date' from users) --select Creation_date_year,Creation_date_month,count(*) as 'num_of_joined_users' --from post_table --group by Creation_date_year,Creation_date_month --order by Creation_date_year,Creation_date_month; --section 11- 11. Find what is the percentage of questions out of the total --number of posts,the percentage of answers out of the total number of posts, --and the percentage of posts which are neither questions nor answers out of the --total number of posts. Display each percentage rounded to 2 decimal places. --solution- --note: post type 1 is question and post type 2 is answer select round(cast((select count(*) from posts where PostTypeId=1)/count(*) as real),2) from posts;