+972 52-548-6969
Blog Stats: SQL
The best part about using a blog based on a database server is that you get to run all sorts of interesting queries against it.
Posts #:
SELECT COUNT(*)
FROM dbo.subtext_Content
Comments #
SELECT COUNT(*),
FeedbackType
FROM dbo.subtext_Feedback
GROUP BY FeedbackType
(FeedbackType: 1 = Comment, 2 = Trackback)
Avg. Comments Per Post:
SELECT AVG(CommentsPerPost)
FROM ( SELECT COUNT(dbo.subtext_Feedback.Id) CommentsPerPost,
dbo.subtext_Content.Id
FROM dbo.subtext_Feedback
RIGHT JOIN dbo.subtext_Content
ON dbo.subtext_Content.Id = dbo.subtext_Feedback.EntryId
WHERE FeedbackType = 1
GROUP BY dbo.subtext_Content.Id
) commentsPerPost
Post with most comments:
SELECT TOP 1
COUNT(dbo.subtext_Feedback.Id) CommentsPerPost,
dbo.subtext_Content.Id
FROM dbo.subtext_Feedback
RIGHT JOIN dbo.subtext_Content ON dbo.subtext_Content.Id = dbo.subtext_Feedback.EntryId
WHERE FeedbackType = 1
GROUP BY dbo.subtext_Content.Id
ORDER BY COUNT(dbo.subtext_Feedback.Id) DESC
Avg. Posts per Month:
SELECT AVG(PostsPerMonth)
FROM ( SELECT DATEADD(year, YEAR(DateAdded) - 1900,
DATEADD(month, MONTH(DateAdded)-1, 0)) Date,
COUNT(*) PostsPerMonth
FROM subtext_Content
GROUP BY MONTH(DateAdded),
YEAR(DateAdded)
) postsPerMonth
Avg. Posts per Week
SELECT AVG(postsPerWeek)
FROM ( SELECT DATEPART(week, dateadded) weekNum,
YEAR(dateadded) [year],
COUNT(*) postsPerWeek
FROM subtext_Content
GROUP BY DATEPART(week, dateadded),
YEAR(DateAdded)
) postsPerWeek
Avg. Comments per Month:
SELECT DATEADD(year, YEAR(DateCreated) - 1900,
DATEADD(month, MONTH(DateCreated) - 1, 0)) Date,
COUNT(*) CommentsPerMonth
FROM subtext_Feedback
WHERE feedbacktype = 1
GROUP BY MONTH(DateCreated),
YEAR(DateCreated)
Comments
It's like you can read my mind! These are exactly the type of stats I want to add to Subtext. In fact, I was thinking of some of these exact stats.
Most Commented Post
Comment preview