```
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