Search code examples
sqlcountdistinct-valuesunique-values

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)