Most-Viewed Posts Using bit.ly

A list of posts using the bit.ly URL shortener.
For more details, see http://meta.stackoverflow.com/q/99136/162705

Q&A for programmers

-- Most-Viewed Posts Using bit.ly
-- A list of posts using the bit.ly URL shortener.
-- For more details, see http://meta.stackoverflow.com/q/99136/162705
select
case when p.PostTypeId = 1 then p.ViewCount else q.ViewCount end as [Views],
p.CreationDate as [Created On],
case when p.PostTypeId = 1 then 'Question' else 'Answer' end as [Post Type],
p.Id as [Post Link],
p.OwnerUserId as [User Link]
from Posts p
left outer join Posts q on q.Id = p.parentId
where
p.Body like '%http://bit.ly%'
order by [Views] desc