Post History

How to easily support time frame grouping in queries?

I had a curiosity about how much the experienced users wait for their questions to be answered on Stack Overflow and had written [a query for it](https://data.stackexchange.com/stackoverflow/query/1404738/percentage-of-experienced-users-questions-that-received-answer-in-less-than-a-wee):

```
SELECT YEAR(q.CreationDate) * 100 + MONTH(q.CreationDate) AS YM, COUNT(1) Cnt 
INTO #a_cte
FROM Posts AS q
   INNER JOIN Users qu ON qu.Id = q.OwnerUserId and qu.Reputation >= 1000
   WHERE q.CreationDate BETWEEN '20190101' AND '20210301'
   AND EXISTS (
     SELECT 1 
     FROM Posts a 
     where a.PostTypeId = 2 and a.ParentId = q.Id and a.Score > 1
     and datediff(day, q.CreationDate, a.CreationDate) <= 7
  )
  AND q.PostTypeId = 1
  GROUP BY YEAR(q.CreationDate) * 100 + MONTH(q.CreationDate);
 
SELECT YEAR(q.CreationDate) * 100 + MONTH(q.CreationDate) AS YM, COUNT(1) Cnt
INTO #q_cte
FROM Posts AS q
 INNER JOIN Users qu ON qu.Id = q.OwnerUserId and qu.Reputation >= 1000
WHERE q.CreationDate BETWEEN '20190101' AND '20210301'
 AND q.PostTypeId = 1
GROUP BY YEAR(q.CreationDate) * 100 + MONTH(q.CreationDate);
 
SELECT CAST(a.YM / 100 AS VARCHAR) + '-' + CAST(a.YM % 100 AS VARCHAR) + '-01', a.Cnt * 100.0 / q.Cnt AS AnsweredRatio
FROM #a_cte a
  JOIN #q_cte q ON q.YM = a.YM
ORDER BY a.YM
``` 

The performance is rather poor and I think it is also related to the fact that I have to compute the year-month and also group by it. This should have been much easier if the data model supplied some "dimension" columns such as year, month, year-month.

I know that enterprise solutions involve reporting databases and cubes, but I am wondering about simpler solutions (less maintenance for medium-sized data volumes). Is adding of [computed columns](https://docs.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-ver15) a good way to support the queries I want. Examples:

- `CreationDateYear = YEAR(CreationDate)`
- `YM = YEAR(q.CreationDate) * 100 + MONTH(q.CreationDate) AS YM`

Or maybe a better approach is to have a job that computes similar columns in separate tables (as opposed to the real-time nature of the computed columns).