0

Please login or register to vote for this query.

(click on this box to dismiss)

Count of accepted questions divided by count of asked question, sorted by date. Only the Creation Date of the question is involved, so it doesn't matter when the question is answered, as long as it is answered and accepted.

Q&A for anime and manga fans

-- Accept rate by day of week

CREATE TABLE #TDayOfWeek(Id INTEGER, Description VARCHAR(10));
INSERT INTO #TDayOfWeek VALUES (1,'Sunday');
INSERT INTO #TDayOfWeek VALUES (2,'Monday');
INSERT INTO #TDayOfWeek VALUES (3,'Tuesday');
INSERT INTO #TDayOfWeek VALUES (4,'Wednesday');
INSERT INTO #TDayOfWeek VALUES (5,'Thursday');
INSERT INTO #TDayOfWeek VALUES (6,'Friday');
INSERT INTO #TDayOfWeek VALUES (7,'Saturday');

CREATE TABLE #AcceptedByDay (Id INTEGER, AcceptCount INTEGER);
INSERT INTO #AcceptedByDay
SELECT Q.DOW, COUNT(Q.DOW)
FROM
(
  SELECT DATEPART(dw, CreationDate) as DOW
    FROM Posts
    WHERE AcceptedAnswerId IS NOT NULL
    AND PostTypeId=1
) Q
GROUP BY Q.DOW;

CREATE TABLE #AskedByDay (Id INTEGER, AskedCount INTEGER);
INSERT INTO #AskedByDay
SELECT Q.DOW, COUNT(Q.DOW)
FROM
(
  SELECT DATEPART(dw, CreationDate) as DOW
    FROM Posts
    WHERE PostTypeId=1
) Q
GROUP BY Q.DOW;


SELECT #TDayOfWeek.Description as 'Day of Week', #AcceptedByDay.AcceptCount, #AskedByDay.AskedCount,
100*#AcceptedByDay.AcceptCount/#AskedByDay.AskedCount as 'Accepted/Asked'
FROM #TDayOfWeek
INNER JOIN #AcceptedByDay ON #AcceptedByDay.Id=#TDayOfWeek.Id
INNER JOIN #AskedByDay ON #AskedByDay.Id=#TDayOfWeek.Id
ORDER BY #TDayOfWeek.Id;

fork query

created nov 4 10 MPelletier