Blog Stats: SQL
time to read 14 min | 2783 words

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)

Tweet Share Share 2 comments
Tags: