Multiple conditions over 2 columns in SQL?

Question

I am given a database with 3 different tables as such, and no sample data:

Basically, I'm trying to find a query for the number of users, for whom the last activity was done with device_name = “phone”, but who also have at least one additional activity with another device by combining data from the 3 tables.

The issue is that I can't include both those conditions in the same where clause because I'm they should be on different rows, i.e. different activites.

What I have so far is:

With CTE as (
    select * 
    from users u 
    join activities a on u.user_id=a.activity_user_id 
    join devices d on a.activity_device_ID=d.device_ID
) 
Select count(a.activity_user_id) 
from CTE 
where (a.activity_is_last='Yes' and d.device_name='phone') 

I almost want to include some sort of when clause, so that WHEN the activity is last I want to check if the device is a phone, but when it's not I want to check if it's not phone. I also want to validate that there are at least 2 rows, is_last and not is_last. How is it possible?

Answer 1

Here I have used two row_number() window function to rank the output of inner query. RNK1 will represent the rank when a.activity_is_last='Yes' and d.device_name='phone' and RNK2 will represent the rank when a.activity_is_last='No' and d.device_name<>'phone'. Then in outer query I have selected first row from both the ranked and checked whether there are two rows or not with having clause and group by.

With CTE as (
    select * ,
    (case when (a.activity_is_last='Yes' and d.device_name='phone') then 
         row_number()over(partition by case when (a.activity_is_last='Yes' and d.device_name='phone') then activity_user_id end order by activity_timestamp desc) end) rnk1,
    (case when (a.activity_is_last='No' and d.device_name<>'phone') then 
         row_number()over(partition by case when (a.activity_is_last='No' and d.device_name<>'phone')  then activity_user_id end order by activity_timestamp desc) end) rnk2
    from users u 
    join activities a on u.user_id=a.activity_user_id 
    join devices d on a.activity_device_ID=d.device_ID
) 
Select activity_user_id
from CTE 
where rnk1=1 or rnk2=1
group by activity_user_id
having count(*)=2

Answer 2

It would be better if you have some sample data. But I think this will work for you:

select * 
     ,case when d.device_name='phone' then 0 else 1 end as HasOtherdevice
into #tmp1
    from users u 
    join activities a on u.user_id=a.activity_user_id 
    join devices d on a.activity_device_ID=d.device_ID


Select a.activity_user_id,sum(HasOtherdevice)as cnt
into #tmp2
from #tmp1
group by a.activity_user_id

select count(a.activity_user_id) 
from #tmp1
where (a.activity_is_last='Yes' and d.device_name='phone') 
     and a.activity_user_id in (select a.activity_user_id
                                from #tmp2
                                where cnt=0)

Answer 3

You can just do two conditional counts in a HAVING clause

select u.user_id
from users u 
join activities a on u.user_id = a.activity_user_id 
join devices d on a.activity_device_ID = d.device_ID
group by u.user_id
having count(case when a.activity_is_last = 'Yes' and d.device_name = 'phone') > 0
   and count(case when a.activity_is_last = 'No' and d.device_name <> 'phone') > 0

---

Strictly speaking, you should not store activity_is_last in the database anyway, as it is defined just by being the latest row ordered by date. (And in any case it should be a bit column.)

So if you didn't have that column, you would use ROW_NUMBER to get the top row:

select u.user_id
from users u 
join (
    select *,
        rn = row_number() over (partition by a.activity_user_id order by a.activity_timestamp desc)
    from activities a
) a on u.user_id = a.activity_user_id 
join devices d on a.activity_device_ID = d.device_ID
group by u.user_id
having count(case when a.rn = 1 and d.device_name = 'phone') > 0
   and count(case when a.rn > 1 and d.device_name <> 'phone') > 0

Answer 4

Perhaps the simplest way is to count in the subquery.

Basically, I'm trying to find a query for the number of users, for whom the last activity was done with device_name = “phone”, but who also have at least one additional activity with another device by combining data from the 3 tables.

If I assume that activity_is_last is true on only one row, then:

with CTE as (
      select *,
             min(device_name) over (partition by user_id) as min_device_name,
             max(device_name) over (partition by user_id) as max_device_name
      from users u join
           activities a
           on u.user_id = a.activity_user_id join
           devices d
           on a.activity_device_ID = d.device_ID
     ) 
Select count(*) 
from cte
where activity_is_last = 'Yes' and device_name = 'phone' and
      min_device_name <> max_device_name ;

Answer 5

The simplest way seems to be to locate the base row(s) using WHERE activity_is_last='Yes' and d.device_name='phone'. If activity_is_last='Yes' occurs once per user then the query returns unique user_id's. Then use EXISTS to only count users with activity where at least 1 device_name is other than 'phone'. Something like this

select count(a.activity_user_id) as num_of_users
from activities a
     join devices d on a.activity_device_ID=d.device_ID
where a.activity_is_last='Yes'
      and d.device_name='phone'
      and exists (select 1
                  from activities aa
                       join devices dd on aa.activity_device_ID=dd.device_ID
                  where a.activity_user_id=aa.activity_user_id
                        and dd.device_name<>'phone');