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:

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

CodePudding user response：

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)

CodePudding user response：

with
max_impressions_per_user_id as 
(
select 
    user_id,
    max(impressions) as max_impressions
from table 
group by user_id
)
select count(*)
from max_impressions_per_user_id 
where max_impressions < 3
;