Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 10 down vote accepted
+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

share|improve this answer
    
Thanks, it won't let me award the bounty until 23 more hours. I've already started the process. +66 –  Daи Jul 15 '13 at 14:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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