0

Please login or register to vote for this query.

(click on this box to dismiss)

Shows which tags bring in the most viewers

Q&A for professional and enthusiast programmers

-- Top Tags by Median ViewCount
-- See For Median http://stackoverflow.com/q/1342898/275404

select tagName, avg(ViewCount) as [Median Views]
From(
  SELECT
   Tags.tagName,
   ViewCount,
   ROW_NUMBER() OVER (
       PARTITION BY Tags.tagName 
       ORDER BY ViewCount ASC) AS RowAsc,
    ROW_NUMBER() OVER (
       PARTITION BY Tags.tagName 
       ORDER BY ViewCount DESC) AS RowDesc
    from Posts 
    inner join PostTags on Posts.Id = PostTags.PostId
    inner join Tags  on PostTags.TagId = Tags.Id
    ) as x
    Where
    RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
    
    
    
Group By tagName
Order by [Median Views] Desc

/*


SELECT
   CustomerId,
   AVG(TotalDue)
FROM
(
   SELECT
      CustomerId,
      TotalDue,
      ROW_NUMBER() OVER (
         PARTITION BY CustomerId 
         ORDER BY TotalDue ASC, SalesOrderId ASC) AS RowAsc,
      ROW_NUMBER() OVER (
         PARTITION BY CustomerId 
         ORDER BY TotalDue DESC, SalesOrderId DESC) AS RowDesc
   FROM Sales.SalesOrderHeader SOH
) x
WHERE 
   RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
GROUP BY CustomerId
ORDER BY CustomerId;

*/

fork query

edited mar 2 12 davrob60

created mar 2 12 jon.doe7339