SQL query to get values by the calendar month-CodePudding

I have the following schema:

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

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

and i want to get a return object that has the industry id along with the number of items but based on the calendar month for the previous two months, not including the current. So i want the number of items in October and the number in November.

What i have, that is not working

SELECT industry_id,
    sum(
        CASE
            WHEN start_date >= (date_trunc('month', start_date) - interval '1 month')::date AND start_date < (date_trunc('month', start_date) - interval '2 month')::date THEN 1
            ELSE 0
        END) AS last_month,
    sum(
        CASE
            WHEN start_date >= (date_trunc('month', start_date) - interval '2 month')::date AND start_date <= (date_trunc('month', start_date) - interval '3 month')::date THEN 1
            ELSE 0
        END) AS prev_month
   FROM phone_clicks
   Group by industry_id

What i am currently getting from the above :

industry_id last_month  prev_month
     4          0           0
     1          0           0
     3          0           0
     2          0           0

What i am expecting:

industry_id last_month  prev_month
     4          1           0
     1          1           2
     3          1           1
     2          1           1

So industry 1, it has two items with start_date in October and 1 in November.

CodePudding user response：

date_trunc('month', ...) always returns the first of the month, so you don't really need the >= .. <= in your CASE expression.

It's enough to compare the month start of "start_date" with the month start of last month, which is date_trunc('month', current_date - interval '1 month')

The query can also be simplified a bit by using the filter operator.

SELECT industry_id, 
       count(*) filter (where date_trunc('month', start_date) = date_trunc('month', current_date - interval '1 month')) as last_month,
       count(*) filter (where date_trunc('month', start_date) = date_trunc('month', current_date - interval '2 month')) as prev_month
FROM phone_clicks
group by industry_id
order by industry_id