5

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.

10
+50

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

  • Thanks, it won't let me award the bounty until 23 more hours. I've already started the process. +66 – Dan Jul 15 '13 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.