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; --section 7- 7. Display the post types which didn’t get score 0 during yesterday --solution- --select PostTypeId from posts --where not exists ( -- select PostTypeId from posts -- where score = 0 -- and CreationDate > cast(getdate()-5 as date) -- and CreationDate < cast(getdate()-4 as date)); select PostTypeId,CreationDate from posts where score = 0 and cast(CreationDate as date) = '2024/11/10';