CODEWITHSUNDEEP
sql-server
G Mohan
G Mohan

Reputation: 61

SQL Server - Case Statement to summarize data

I have a problem to uniquely identify a given user based on the type of the device they log into.

My Dataset looks like this

FIRST_NM LAST_NM LOGIN_MONTH    DEVICE_TYP
------------------------------------------
JOHN      DOE     4         Desktop
JOHN      DOE     5         Desktop
JOHN      DOE     6         Phone
JOHN      DOE     6         Desktop
JANE      DOE     4         Phone
JANE      DOE     5         Desktop
JANE      DOE     5         Phone
JANE      DOE     6         Desktop
JANE      DOE     6         Phone

And I want to summarize the results based on the last 3 login patterns.

FIRST_NM LAST_NM    DEVICE_TYP
-------------------------------------------------------
JOHN      DOE       Active Desktop User
JANE      DOE       Active on Both Desktop and Mobile

dataset description

Upvotes: 0

Views: 209

Answers (2)

iSR5
iSR5

Reputation: 3498

I'm not sure which part of equality your logic is supposed to be. In your sample results, JANE used Phone 3 times, and Desktop 2 times. So, Jane should be a Mobile user. However, you might take the average usage of a user devices by dividing the number of devices usage on number of total logins, which will give you a good view to determine user preferable device type (most used).

So, in your results, John's average of uses will be around 75% on Desktop and 25% on phone, which means he prefers using Desktop over Mobile device. While, Jane has an average of 40% on Desktop and 60% Phone, meaning Jane is using both devices consecutively.

If this is the way your logic works, then you could do something like this : 

SELECT *
,
    CASE 
        WHEN AvgDesktopUsage >= 70 AND AvgPhoneUsage <= 30 THEN 'Active Desktop User'
        WHEN AvgPhoneUsage >= 70 AND AvgDesktopUsage <= 30  THEN 'Active Mobile User'   
        ELSE 'Active on Both Desktop and Mobile'
    END 
FROM (
SELECT *
,   ( Desktop / CAST(TotalLogins AS FLOAT) ) * 100 AvgDesktopUsage
,   ( Phone / CAST(TotalLogins AS FLOAT) ) * 100 AvgPhoneUsage
FROM (
SELECT 
    FIRST_NM
,   LAST_NM
,   SUM(CASE WHEN DEVICE_TYP = 'Desktop' THEN 1 ELSE 0 END) Desktop
,   SUM(CASE WHEN DEVICE_TYP = 'Phone' THEN 1 ELSE 0 END) Phone
,   COUNT(*) TotalLogins
FROM yourTable 
WHERE 
    LOGIN_MONTH >=  MONTH(GETDATE()) - 3
AND LOGIN_MONTH <=  MONTH(GETDATE()) 
GROUP BY
    FIRST_NM, LAST_NM
HAVING 
    COUNT(*) > 2
) D
) E

You could change the average logic to fit your needs. Also, LOGIN_MONTH is not enough to represent the date or time, it has to have at least an year to distinguish months for each year (at least). (maybe there is a column represent a year ? )...

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35593

You may use row_number() over(...) to determine last 3 logins (sort of, see note below):

select
      FIRST_NM
    , LAST_NM
    , max(case when rn = 1 then DEVICE_TYP end) as DEVICE_TYP_1
    , max(case when rn = 2 then DEVICE_TYP end) as DEVICE_TYP_2
    , max(case when rn = 3 then DEVICE_TYP end) as DEVICE_TYP_3
from (
    select
    *
    , row_number() over(partition by FIRST_NM,LAST_NM order by LOGIN_MONTH DESC) as rn
    from mytable
    ) d
where rn <= 3
group by
      FIRST_NM
    , LAST_NM

which produces this result:

  FIRST_NM   LAST_NM   DEVICE_TYP_1   DEVICE_TYP_2   DEVICE_TYP_3  
  ---------- --------- -------------- -------------- -------------- 
  JANE       DOE       Desktop        Phone          Desktop       
  JOHN       DOE       Phone          Desktop        Desktop

A column called 'login_month' isn't a very exact way of deciding the "last 3 logins", what happens when you are in January of next year and the previous logins were in month 12 and 11? Don't you have a date field in your data?

Also see: http://rextester.com/GJBHH85148

Upvotes: 1

Related Questions