Sql-server – Count Most Recent Browser Used By All Users

sql-server-2008-r2

I have a table with columns Type, Login, and Timestamp. It has been collecting information about the type of browsers users log into the system with for years. Obviously many of the users have since upgraded their browsers. I do not want to consider data for old browsers when users have upgraded. Here is the query I currently have to get a count of how many people use which browser:

select 
  Type, 
  count(Type) as Count 
from 
  (select 
    distinct Login, 
    Type 
   from BROWSER) as q 
group by Type

This appears to work fine, but I have a lot of old browsers that I know are no longer being used:

yucky - old browsers no one uses anymore

How would I only select the most recent browser used by each user based on timestamp (or Browser_ID could work also since they are written sequentially). I also intend to ignore data by users who have not logged in during the past year, but that is simple to add later (I only mention it so that no one brings up why I would want browser data that is old for inactive users – I do plan on addressing that as well but I know how to do that already).

If anyone is up for a challenge, there are some users who log in from a couple different computers – or perhaps from their phone and from their desk. It would be great if I could determine that they frequently log in from two different places and also collect that data, but this might be too complicated. If anyone figured out how to add this complicated step, I'll gladly award a 50 point bounty to the answer.

Best Answer

Not your exact requirements (instead of ignoring data from users inactive the last year, the following queries ignore all data older than a year) but you can get ideas and change the conditions to more complex if needed:

  • Count all Login and browser Type combinations, up to a year ago. 

    ; WITH cte AS
( SELECT    *
  FROM      Browser
  WHERE     Timestamp >= DATEADD(Year, -1, GETDATE())
)
SELECT      Type,
            Count = COUNT(DISTINCT Login)
FROM        Browser
GROUP BY    Type ;

  • Count only most recent browser Type per Login, up to a year ago.

    ; WITH cte AS
( SELECT    Type,
            Login,
            Rn = ROW_NUMBER() OVER (PARTITION BY Login
                              ORDER BY Timestamp DESC)
  FROM      Browser
  WHERE     Timestamp >= DATEADD(Year, -1, GETDATE())
)
SELECT      Type,
            Count = COUNT(*)
FROM        cte
WHERE       Rn = 1
GROUP BY    Type ;

  • Count up to 5 recent browser Type per Login, and up to a year ago.

    ; WITH cte AS
( SELECT    Type,
            Login,
            Rn = ROW_NUMBER() OVER (PARTITION BY Login
                                    ORDER BY MAX(Timestamp) DESC)
  FROM      Browser
  WHERE     Timestamp >= DATEADD(Year, -1, GETDATE())
  GROUP BY  Login, Type
)
SELECT      Type,
            Count = COUNT(*)
FROM        cte
WHERE       Rn <= 5
GROUP BY    Type ;

Tests at SQL-Fiddle

Related Question