How to pick Last Non Direct source using SQL

Question

I have following touch points:

Session Number  Source
1               direct
2               social
3               paid
4               direct
5               direct
6               direct

I need to pick the Last-Non Direct source… which is paid in this example (session 3).

Take into consideration that it can be cases that have just direct and then direct should be displayed. So basically we need to have the same as Google Analytics does : Last Non-Direct Attribution.

What SQL code can do that?

Answer 1

For your example, this should work

SELECT TOP 1 * FROM [Your_Table] 
WHERE Source <> 'direct'
ORDER BY [Session Number] DESC

Similar question

EDIT1:

SELECT * FROM [Your_Table] 
WHERE 
--checking if anything else except 'direct' exists, if so, take the last one non direct
(EXISTS (SELECT * FROM [Your_Table] WHERE source <> 'direct') AND source <> 'direct') 
OR 
--if nothing except direct doesn't exist, just take the last row
NOT EXISTS (SELECT * FROM [Your_Table] WHERE source <> 'direct')
ORDER BY [session number] DESC
LIMIT 1;

Answer 2

I need to pick the Last-Non Direct source… which is paid in this example (session 3).

In Postgres you would use:

select t.*
from t
where Source <> 'direct'
order by session_number desc
limit 1;

Instead of limit, you can also use:

fetch first 1 row only

If you don't want filtering, you can use:

select t.*
from t
where Source <> 'direct'
order by (source = 'direct') asc,  -- put last
         session_number desc
limit 1;

Or if you just want the session number, you can use conditional aggregation:

select coalesce( max(session_number) filter (where source <> 'direct'),
                 max(session_number)
from t;