CODEWITHSUNDEEP
sqlpostgresqlcount
Rafael Higa
Rafael Higa

Reputation: 725

mean of count in an interval

I have a table like this:

item_id    customer_id    date
12345      a              201912
12345      a              202001
12345      a              202002
12345      b              202002
12345      c              202002
67890      f              201912
67890      e              202001

I want to count the number of customer_id who bought each item in each month, and then get the mean of customers who bought each item during the interval of dates in the table (three months, in this case). The output should be:

item_id    customer_id
12345      (1+1+3)/3=5/3
67890      (1+1+0)/3

I'm stucked in this problem. Could you help me? Thanks!

Upvotes: 1

Views: 27

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You can do this using count(distinct) in one step:

select item_id, count(*) / count(distinct date)
from t
group by item_id;

This divides the total count by the number of months -- exactly your calculation.

If your date is really stored as a date, you can use date_trunc():

select item_id, count(*) / count(distinct date_trunc('month', date))
from t
group by item_id;

Upvotes: 1

Related Questions