Q&A for ebook publishers and readers
--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- select Id from users where Id < 0;