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:

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 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).

posted over 3 years ago

CC BY-SA 4.0

3y ago

Alexei‭

5003 reputation 112 101 690 487