Trying to get the count for the number of times an id shows up in different timeframes

So i want to get the number of times an id shows up in different time frames. Here is the schema:

CREATE TABLE phone_clicks (
id integer NOT NULL,
date date NOT NULL,
industry_id integer NOT NULL,
clicks integer DEFAULT 0 NOT NULL
);

insert into phone_clicks(id, date, industry_id)
values
(1, '2021-12-03', 1),
(2, '2021-12-03', 2),
(3, '2021-12-02', 3),
(4, '2021-12-01', 1),
(5, '2021-12-01', 3),
(6, '2021-12-01', 4),
(7, '2021-12-02', 1),
(8, '2021-12-02', 2);

Here is where i am for now, but all this is doing is getting the full count for each id.

    select  industry_id
,       count(case when current_date <= date then 1 else 0 end) as today 
,       count(case when current_date-1 <= date and
                      date < current_date then 1 else 0 end) as yesterday
,       count(case when current_date-4 <= date and 
                      date < current_date-1 then 1 else 0 end) as last3days
from    phone_clicks
group by
        industry_id

This is returning me this:

industry_id today   yesterday   last3days
4             1        1            1
1             3        3            3
3             2        2            2
2             2        2            2

Which is just the number of times the id shows up in the table at all each time. I want the number of times id 4 shows up today, yesterday and in the last 3 days. Should be 0 for today and yesterday

CodePudding user response：

When you use a COUNT then it'll count anything that's not NULL.
And 0 isn't NULL, so it gets counted.

If you remove the ELSE in the CASE WHEN then only the succesful conditions are counted.

select  industry_id
, count(case when t.date = current_date
             then id end) as today 
, count(case when t.date = current_date-1
             then id end) as yesterday
, count(case when t.date between current_date-4 
                             and current_date-2
             then id end) as last3days
from    phone_clicks t
group by
        industry_id

CodePudding user response：

You can use the filter (where ...) pattern for use condition in windows function, it is a common and better case pattern

Demo

select
  industry_id,
  count(*) filter (where current_date <= date) as today,
  count(*) filter (where current_date-1 <= date and date < current_date) as yesterday,
  count(*) filter (where current_date-4 <= date and date < current_date-1) as last3days
from
  phone_clicks
group by
  industry_id;