0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people studying math at any level and professionals in related fields

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

select posts.PostTypeId,PostTypes.Name,count(*) as 'num_of_posts' from posts
left join PostTypes
on posts.PostTypeId=PostTypes.Id
group by posts.PostTypeId,PostTypes.Name
order by 1;

select PostHistory.PostHistoryTypeId as 'History_post_type_id',
PostHistoryTypes.Name as 'History_post_type',count(*) as 'num_of_posts'
from PostHistory
left join PostHistoryTypes
on PostHistory.PostHistoryTypeId=PostHistoryTypes.Id
group by PostHistory.PostHistoryTypeId,PostHistoryTypes.Name
order by 1;

fork query

edited nov 15

created nov 14 user28265485