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.
Advertisement
Answer
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