'Filter out records that exceed certain count

was hoping someone could advise on the following problem.

I have a table similar to below and am trying to filter out all IDs that have the same subject and date (not just dedupe but remove complete) from my output.

ID Date Subject Type
abc 1/22/22 Welcome> Email
def 1/23/22 Offer! Call
xyz 1/22/22 Welcome> Email

So in the example above we would want to remove both rows with subject, "Welcome>"

I am able to write a query that shows me the count of IDs per subject and date, but stuck finding a way to remove these from my final output.

  select distinct 
  activitydate,
  name,
  subject,
  count (id)
  from activity
  where activity.activitydate between X and Y'
  and type IN ('Email')
  group by 1,2,3
  having count (id) > 30
  order by count (id) desc

Please let me know if any further details are needed and thank you in advance!

sqlsnowflake-cloud-data-platform


Solution 1:[1]

-- Filter out all IDs that have the same subject and date
SELECT
    Date,
    Subject,
    COUNT(*)
FROM
    activity
GROUP BY 
    Date,
    Subject
HAVING 
    COUNT(*) < 2

Solution 2:[2]

If you want to remove the rows where the COUNT by ID and SUBJECT is > 1 then you can do some thing like this.

-- untested

SELECT * FROM activity 
WHERE (ID,SUBJECT) NOT IN 
(SELECT ID,SUBJECT FROM activity 
GROUP BY ID, SUBJECT HAVING COUNT(*) > 1)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Himanshu Kandpal

Related Questions