Q&A about the site for users of Iota, the open-source cryptocurrency for IoT that does not use a blockchain
--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)) select * from ranked_score_per_user where post_score_rank <= 3; --select top (100) row_number() over (order by Id), *, --case -- when ClosedDate-CreationDate > 100 then 'Active_post' -- else 'Non_active_post' -- end as 'Active/not_active' --from posts;