del.icio.us/mhinze

posts per month

select
    dateadd(month, datediff(month, 0, [Time]),0)
    , count(*)
from posts
group by dateadd(month, datediff(month, 0, [Time]),0)
order by dateadd(month, datediff(month, 0, [Time]),0) asc

domains all time

select top 10
    count(*) as [Count],
    dbo.RegexGroup([Href], N'http(|s)?://(?<domain>([\w-]+\.)*[\w-]+)', N'domain') as [Domain]
from [posts]
group by dbo.RegexGroup( [Href], N'http(|s)?://(?<domain>([\w-]+\.)*[\w-]+)', N'domain')
order by count(*) desc

206    blogs.msdn.com
138    weblogs.asp.net
114    www.codeproject.com
113    www.microsoft.com
105    www.codeplex.com
89    msdn.microsoft.com
85    msdn2.microsoft.com
70    code.google.com
51    www.sharepointblogs.com
38    www.google.com

domains in 2008

select top 10
    count(*) as [Count],
    dbo.RegexGroup([Href], N'http(|s)?://(?<domain>([\w-]+\.)*[\w-]+)', N'domain') as [Domain]
from [posts]
where [time] > '2008-1-1'
group by dbo.RegexGroup( [Href], N'http(|s)?://(?<domain>([\w-]+\.)*[\w-]+)', N'domain')
order by count(*) desc

34    code.google.com
26    www.codeplex.com
19    codebetter.com
19    weblogs.asp.net
17    blogs.msdn.com
10    haacked.com
10    www.hanselman.com
10    www.lostechies.com
9    www.codeproject.com
8    blogs.hibernatingrhinos.com

tags

select top 10 count(*), name from tags group by name order by count(*) desc 

768    asp.net
749    programming
727    .net
439    sharepoint
390    c#
329    javascript
254    css
214    webdesign
196    google
195    ajax

the long tail

select count (distinct (dbo.RegexGroup([Href], N'http(|s)?://(?<domain>([\w-]+\.)*[\w-]+)', N'domain')))
from [posts]

2695

select count (distinct Name) from tags

2083

select count(*) from posts

5148