0

Please login or register to vote for this query.

(click on this box to dismiss)

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-
with organized_table as (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)
select Id,OwnerUserId,PostTypeId,CreationDate from organized_table
where Diff_from_prev_post_type < 30 or Diff_from_next_post_type < 30;

with organized_table as (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)
select OwnerUserId,PostTypeId,Diff_from_prev_post_type from organized_table
where Diff_from_prev_post_type < 30;

view current version

edited nov 15

created nov 14 user28265485