Q&A for users and developers of the Cardano cryptocurrency ecosystem
--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,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;