Контрольная работа
.docx
Задание 1.
select ParentId as QuestionId
from Posts where PostTypeId = 2
group by ParentId
having max(CreationDate) - min(CreationDate) = (
select top 1 max(CreationDate) - min(CreationDate)
from Posts where PostTypeId = 2
group by ParentId
order by max(CreationDate) - min(CreationDate) desc
)
Задание 2.
select
year(LastCreationDate) as lYear,
month(LastCreationDate) as lMonth,
count(OwnerUserId)
from (
select
OwnerUserId,
max(CreationDate) as LastCreationDate
from Posts
where not OwnerUserId = -1
group by OwnerUserId
) as tmp
group by year(LastCreationDate), month(LastCreationDate)
order by year(LastCreationDate), month(LastCreationDate)
Задание 3.
select
count(distinct OwnerUserId) as UserCount
from (
select
year(CreationDate) as y,
OwnerUserId,
count(Id) as PostsCount
from Posts
group by year(CreationDate), OwnerUserId
) as tmp
where PostsCount >= 100
Задание 4.
select 1.0 * count(UserIdNovemberPost) / count(UserIdDecemberFirstPost)
from (
select OwnerUserId as UserIdDecemberFirstPost
from Posts
group by OwnerUserId
having year(min(CreationDate)) = 2019 and month(min(CreationDate)) = 12
) as tmp0
left join (
select OwnerUserId as UserIdNovemberPost
from Posts
where year(CreationDate) = 2020 and month(CreationDate) = 11
group by OwnerUserId
) as tmp1 on UserIdDecemberFirstPost = UserIdNovemberPost