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 or ClosedDate is null)) --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 or ClosedDate is null then 'Active_post' else 'Non_active_post' end as 'Active/not_active' from posts;