SQL query unique user by device type-CodePudding

I am trying to replicate a report that we receive through our vendor for digital banking. This reports gives me the unique users that logged into online banking and breaks it down into 3 groups: users that logged in via phone/tablet only, users that logged in via desktop only, and users that logged in with multi-devices (phone/tablet and desktop). I already wrote a basic code that gives me a simple table that looks like:

USER_ID	SESSION
1234	PHONE
1234	TABLET
1234	DESKTOP
5678	DESKTOP
5678	DESKTOP
9101	PHONE
9101	TABLET

I am pretty sure I have to create a subquery or CTE but I am not sure how to build it to give me total unique user count for each group (phone or tablet, desktop, and multi-device).

I am stuck on how to get this table above to give me this:

PHONE/TABLET	DESKTOP	Multi-User
1	1	1

Thanks in advance!!

CodePudding user response：

Yes , you can try doing this with help of cte some thing like below :

WITH cte AS (
  SELECT user_id, session, DENSE_RANK() OVER (PARTITION BY user_id ORDER BY session) rn
  FROM xyz_table_name
)
select 
  SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) as "Phone/Tablte",
  SUM(CASE WHEN rn > 1 THEN 1 ELSE 0 END) as "Multi-User"
FROM cte
GROUP BY user_id

CodePudding user response：

You can use bellow code if you want the user who used different platform (desktop or phone or tablet) to be counted in all cases :

    with cte1 as (
    select userid,  string_agg(distinct device,',') device from deviceuser
    group by 1),
    cte2 as(
    select 
    case when device in ('DESKTOP','PHONE','TABLET') then 1 else 0 end Multi_User,
    case when device in ('PHONE','TABLET') then 1 else 0 end "Phone/tablet",
    case when device in ('DESKTOP') then 1 else 0  end DESKTOP
    from cte1
    group by device)
    select SUM(Multi_User) Multi_User ,SUM("Phone/tablet") "Phone/tablet",SUM(DESKTOP) DESKTOP
    from cte2

but if you want to get users counted based on activity use this code: ie : user 1 used desktop and phone he will be counted in multi and not in desktop once and phone again

with cte1 as (
select userid,  string_agg(distinct device,',') device from deviceuser
group by 1),
cte2 as(
select 
case when device ='DESKTOP,PHONE,TABLET' then 1 else 0 end Multi_User,
case when device='PHONE,TABLET' then 1 else 0 end "Phone/tablet",
case when device='DESKTOP' then 1 else 0  end DESKTOP
from cte1
group by device)
select SUM(Multi_User) Multi_User ,SUM("Phone/tablet") "Phone/tablet",SUM(DESKTOP) DESKTOP
from cte2