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;


--section 7- 7.	Display the post types which didn’t get score 0 during yesterday

--solution-
select Id from posttypes
where Id not in (
            select distinct PostTypeId from posts
            where score = 0
            and CreationDate > '2024/11/08'
            and CreationDate < '2024/11/09');

view current version

edited nov 13

created nov 12 user28265485