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-
select OwnerDisplayName,year(CreationDate) as 'Year',
sum(AnswerCount) as 'Yearly total answers'
from posts
group by OwnerDisplayName,year(CreationDate)
having sum(AnswerCount) > 10;

view current version

edited nov 12

created nov 12 user28265485