0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for power users of web applications

select top (50) * from posts;
select top (50) * from users;

--section 5- 5.	For each user, determine whether he accessed the system in the last
--60 days. Display the user’s id, DisplayName, and the value ‘Active’ / ‘Not Active’
--according to the user’s last access date. Display only the 200 with the latest
--access dates.

--solution-
--select top (200) Id,Displayname,LastAccessDate,
-- case
--  when LastAccessDate > (cast(getdate()-60 as date)) then 'Active'
--  else 'Not Active'
-- end as 'Activity'
--from users
--order by LastAccessDate desc;


--section 6- 6.	Find all of the users which their posts in a specific year
--got more than 10 answers in total. Display the user’s display name,
--the relevant year and the cumulative number of answers the user received
--during this year.

--solution-
with posts_users_merged as (
 select users.Id as 'User_id',users.DisplayName as 'User_name',
 posts.CreationDate as 'Post_date',posts.AnswerCount as 'Answer_count' from posts
 left join users
 on posts.OwnerUserId = users.Id
 )
select User_name,year(Post_date) as 'Year',
sum(Answer_count) as 'Yearly total answers'
from posts_users_merged
group by User_name,year(Post_date)
having sum(Answer_count) > 10;

view current version

edited nov 12

created nov 12 user28265485