Search code examples
sqlhivegaps-and-islands

SQL Find following session - different logic than cross join

I have a set of data that stores two types of sessions. It is mobile data usage versus wifi data usage.

ID  Session_Type
1  Cell
2  WiFi
3  Cell
4  Cell
5  WiFi

.
.
.
.
1000 Cell
1001 WiFi

Desired Results

Cell_ID. Next_WiFi_sess_id
1        2
3        5
4        5 
.
.
1000    1001

I have gotten to the extent of joining the table by itself and done such that an id is > than the wifi id, but I am sure if this is perfect solution. Can you do this in a Lag for better performance?

select a.id, b.id
from 
table a
join table b
where a.id > min(b.id)
Solution

  • You can use window functions -- specifically, a cumulative minimum:

    select t.*
from (select t.*,
             min(case when session_type = 'WiFi' then id end) over (order by id rows between current row and unbounded following) as next_wifi_id
      from t
     ) t
where session_type = 'Cell';