CODEWITHSUNDEEP
sql
Andrew Sarrazin
Andrew Sarrazin

Reputation: 11

How to write an excludes if exists query

I need to write a query to showcase users who visited my website, unless they visited in 2008.

So if they visited in the year 2008, I do not want them part of the chart, even if they visited in different years.

How can I write a query that excludes all user activity and data in my table if they visited in 2008?

I have

Unique Username User ID Date

Upvotes: 1

Views: 51

Answers (3)

leftjoin
leftjoin

Reputation: 38325

One more method is using analytics function:

select ...
from 
(
select t.*, sum(case when date between '2008-01-01' and '2008-12-31' then 1 else 0 end) over (partition by userid) as cnt_2008
  from t
) s
where s.cnt_2008 = 0

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

One method is not exists:

select . . .
from t
where not exists (select 1
                  from t t2
                  where t2.userid = t.userid and
                        t2.date >= '2008-01-01' and
                        t2.date < '2009-01-01'
                 );

Upvotes: 1

Leszek Mazur
Leszek Mazur

Reputation: 2531

Another method is EXCEPT:

SELECT DISTINCT "User"
FROM "t"
EXCEPT
SELECT "User"
FROM "t"
WHERE "Date" BETWEEN '2008-01-01' and '2009-01-01'

Upvotes: 0

Related Questions