SQL Server: Top 10 salespeople per week - and previous rankings

Question

select * 
from 
( 
     select year, 
            week,  
            salesperson,
            count(*) as transactions,  
            rank() over(partition by week order by count(*) desc) as ranking 
     from sales
     where year = '2010',               
     group by year,  
              week, 
              salesperson  

) temp 
where ranking <= 10

The query returns a list of the top 10 salespeople (in terms of # of transactions) for each week of the year.

How can I go about adding columns to my results for:

1. Previous week's ranking for that salesperson
2. Total weeks in the Top 10 this year
3. Consecutive weeks in the Top 10 (starting at week 1)
4. Consecutive weeks in the Top 10 (starting in previous year, if possible)

Can you give any general advice on how to go about these sorts of problems?

PS: Using SQL server 2008

Answer 1

My advice is to do the other queries separately in views and then join them in by saleperson (which I assume is key)

The logic is this query is nice and clean and easy to follow. Otherwise - I think the way to attack this would be to start writing TSQL functions to calculate the other values, but I think those functions will have the queries in them anyway.

Answer 2

Actually, I'm not convinced that Views are the best way to go. You can do this sort of logic in CTE's and combine the entire thing into a single query. For example, here is what I have for everything except the consecutive logic:

;With 
    SalesDateParts As
    (
        Select DatePart(wk, SaleDate) As WeekNum, DatePart(yy, SaleDate) As [Year], SalesPersonId
        From #Sales
    )
    , SalesByWeek As
    (
        Select [Year], WeekNum, SalesPersonId, Count(*) As SaleCount
            , RANK() OVER( PARTITION BY [Year], [WeekNum] ORDER BY Count(*) DESC ) As SaleRank
        From SalesDateParts
        Group By [Year], WeekNum, SalesPersonId
    )
    , PrevWeekTopSales As
    (
        Select [Year], [WeekNum], SalesPersonId, SaleCount
        From SalesByWeek
        Where [Year] = DatePart(yyyy, DateAdd(d, -7, CURRENT_TIMESTAMP))
            And WeekNum = DatePart(wk, DateAdd(d, -7, CURRENT_TIMESTAMP))
    )
    , WeeksInTop10 As
    (
        Select SalesPersonId, Count(*) As Top10Count
        From SalesByWeek
        Where SaleRank <= 10
        Group By SalesPersonId
    )
Select *
From Salespersons
    Left Join WeeksInTop10
        On WeeksInTop10.SalesPersonId = SalesPersons.SalesPersonId
    Left Join PrevWeekTopSales
        On PrevWeekTopSales.SalesPersonId = SalesPersons.SalesPersonId

The logic for "consecutive" is probably going to require a calendar table which contains a value for every day along with columns for the given date's year and week.