Combine two queries that are calculated by month

Question

Inspired by this question about Meta , I wrote two queries in the stack data explorer, which shows the total number of Questions asked per month on SO, and the other, Bounties, awarded by month . How can I combine them so that I have the result in a single query? I would like to see Year, Month, Questions, Bounty and Sum in one report.

Questions are recorded in the Posts table, where PostTypeId = 1, but slots are recorded in the Votes table, where VoteTypeId = 9.

Answer 1

I wrote this in notepad and did not work with Data Explorer on SO.

select Isnull(V.Year, P.Year) As Year,
Isnull(V.Month, P.Month) As Month,
Isnull(V.Bounties, 0) As Bounties,
Isnull(V.Amount,0) As Amount ,
P.Questions
FROM
(
select
datepart(year, Posts.CreationDate) Year,
datepart(month, Posts.CreationDate) Month,
count(Posts.Id) Questions
from Posts
where PostTypeid = 1 -- 1 = Question
group by datepart(year, Posts.CreationDate), datepart(month, Posts.CreationDate)
) AS P
left JOIN
(
select
datepart(year, Votes.CreationDate) Year,
datepart(month, Votes.CreationDate) Month,
count(Votes.Id) Bounties,
sum(Votes.BountyAmount) Amount
from Votes
where VoteTypeId = 9 -- 9 = BountyAwarded
group by datepart(year, Votes.CreationDate), datepart(month, Votes.CreationDate)
) AS V
ON P.Year = V.Year AND P.Month = V.Month
order by P.Year, P.Month