Контрольная работа

-- Найти вопрос, между первым и последним ответом
-- на который прошло наибольшее количество времени.


select top 10 ParentId, max(CreationDate), min(CreationDate)
from Posts where PostTypeId = 2
group by ParentId




--select QuestionId
--from (
select * --, first_value(DeltaTime) over (
-- order by DeltaTime desc
-- ) as FirstDeltaTime
from (
select top 100
ParentId as QuestionId,
max(CreationDate) - min(CreationDate) as DeltaTime,
first_value(max(CreationDate) - min(CreationDate)) over (
order by max(CreationDate) - min(CreationDate) desc
) as FirstDeltaTime
from Posts where PostTypeId = 2
group by ParentId
) as tmp
--) as tmp
--where DeltaTime = FirstDeltaTime


-- Найти вопрос, между первым и последним ответом
-- на который прошло наибольшее количество времени. 1033

select top 1
ParentId as QuestionId
from Posts where PostTypeId = 2
group by ParentId
order by max(CreationDate) - min(CreationDate) desc



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
)




-- FIXME: timeout

select
ParentId as QuestionId,
max(CreationDate) - min(CreationDate) as DeltaTime
from Posts where PostTypeId = 2
group by ParentId
having max(CreationDate) - min(CreationDate) = (
select first_value(max(CreationDate) - min(CreationDate)) over (
order by max(CreationDate) - min(CreationDate) desc
)
from Posts where PostTypeId = 2
)




-- предыдущий?

select top 1
ParentId as QuestionId
from Posts where PostTypeId = 2
group by ParentId
order by max(CreationDate) - min(CreationDate) desc




-- Вывести для для каждого месяца каждого года сколько
-- пользователей написали свой последний пост в этом месяце.

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)



-- Сколько пользователей написали не менее 100 постов за год (за любой год). // 28735

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



-- Найти какая доля пользователей, из написавших свой первый
-- пост в декабре 2019, в ноябре 2020 что-то написали.

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










-- Вывести все пары тегов, которые никогда не использовались вместе в одном посте.