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 --with all_counts as ( --select (select count(*) from posts where PostTypeId=1) as q_counts, --(select count(*) from posts where PostTypeId=2) as a_counts, --(select count(*) from posts where PostTypeId != 1 and PostTypeId != 2) as other_posts_count, --count(*) as total_posts from posts) --select round(cast(q_counts as real)/total_posts,4)*100 as 'Questions_perc', --round(cast(a_counts as real)/total_posts*100,2) as 'Answers_perc', --round(cast(other_posts_count as real)/total_posts,4)*100 as 'Other_post_types_perc' --from all_counts; --section 12- 12. Answer the following: --a. Create a user profile using a temporary table. For each user, the profile --will include the user id, his display name and the number of posts he is the --owner of. --solution- --with user_post_count as ( -- select OwnerUserId,count(*) as 'num_of_posts' from posts -- group by OwnerUserId) --select users.Id,users.DisplayName,user_post_count.num_of_posts from users --left join user_post_count --on users.Id=user_post_count.OwnerUserId; --section 13- 13. Display the number of posts from each post type. Refer to --both posts and PostHistory tables. For records from PostHistory, add the prefix --‘History’ to the post type. --solution- --select top (50) * from PostHistoryTypes; select distinct Name from PostHistoryTypes; --select top (50) * from posts; select top (50) * from PostHistory;