0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for users of Ethereum, the decentralized application platform and smart contract enabled blockchain

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 CreationDate > '2024/11/05';

view current version

edited nov 12

created nov 12 user28265485