[SOLVED] SQL how to count unique values that meet specific criteria, and exclude them from the count if their repeating duplicates don't meet my criteria

SQL how to count unique values that meet specific criteria, and exclude them from the count if their repeating duplicates don't meet my criteria

I have a table with user_id's and number of impressions they received during a certain period of time. A new record is added to the table when there is a new impression. Ex below:

user_id	impressions
#1	0
#2	0
#3	1
#3	2
#3	3
#2	1

Question: how to count unique user_id's, who received less than 3 impressions during this period of time using SQL?

If I use COUNT DISTINCT + WHERE impressions < 3, I will get the result

user_id's = 3 (#1, #2, #3), as it will count the first occurrence of #3 that meets my criteria and count it in.

But the right answer would be user_id's = 2 (#1 and #2) because only they received less than 3

Solution

Something like this should work

select user_id, sum(impressions) as impressions
from $your_table
where $your_date_field between $start_period and $end_period -- period of time
group by user_id
having sum(impressions) < 3

Replace "$" table/fields with real ones (unknown in your question)