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

--part 2 (in addition)-
--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;


--section 10- 10.	Answer the following:
--a.	Use a temporary table in order to save additional details regarding each
--user’s creation date in the system. For each user, save the user’s id,
--his display name and creation date. In addition, extract each part
--(day, month, year) of the creation date, and the last day in the creation month.  
--b.	Use the temporary table you created and find how many users joined the
--system in each month (pay attention: June 2016 is defined as different month
--than June 2017).

--solution-
--with post_table as (
--select Id,DisplayName,CreationDate,year(CreationDate) as 'Creation_date_year',
--month(CreationDate) as 'Creation_date_month',day(CreationDate) as 'Creation_date_day',
--eomonth(cast(CreationDate as date)) as 'last_day_of_month_creation_date' from users)
--select Creation_date_year,Creation_date_month,count(*) as 'num_of_joined_users'
--from post_table
--group by Creation_date_year,Creation_date_month
--order by Creation_date_year,Creation_date_month;


--section 11- 11.	Find what is the percentage of questions out of the total
--number of posts,the percentage of answers out of the total number of posts,
--and the percentage of posts which are neither questions nor answers out of the
--total number of posts. Display each percentage rounded to 2 decimal places.

--solution-
--note: post type 1 is question and post type 2 is answer
--select count(*) from posts
--where PostTypeId=1;

--select count(*) from posts;

select count(*) as q_counts from posts
where PostTypeId=1;
select count(*) as a_counts from posts
where PostTypeId=2;
select count(*) as other_posts_counts from posts
where PostTypeId != 1 and PostTypeId != 2;
select count(*) from posts as total;

view current version

edited nov 15

created nov 14 user28265485