0

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

katrin_melody
  • 3
  • 3

2 Answers2

0

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)

James
  • 2,954
  • 2
  • 12
  • 25
  • thank you! This one gives an "Aggregation of aggregation" mistake though. – katrin_melody Dec 02 '22 at 10:04
  • You are welcome. Basically, if you want to filter your data once aggregated, you must use the `having` clause. The `where` clause applies before the grouping stage. If the answer is correct please mark it. Thank you – James Dec 02 '22 at 10:21
  • And what if I want to get a result of those users grouping them by month (for example: 3 users (with imp < 3) in May; 5 users in June; 2 users in July)? – katrin_melody Dec 02 '22 at 12:41
  • Then you need to `count(user_id)`, `sum(impressions)` and show and group by `year($your_date_field)`, `month($your_date_field)` – James Dec 02 '22 at 12:43
0
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
;
Andrew Parrott
  • 21
  • 4