0

Please login or register to vote for this query.

(click on this box to dismiss)

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;


--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;

view current version

edited nov 14

created nov 14 user28265485