Calculate difference between SUM over last 30 days and the SUM for 30 days preceding this period (days 30-60) in Postgres

Question

Every day we store the number of jobs posted for hundres of thousands of companies.

We need to query:

• The SUM of jobs_posted for the last 30 days for each company;
• The DELTA between jobs posted in the last 30 days to the previous 30 day period for each period.

Is there a faster, more efficient way in Postgres 9.6+ to create this query and are there indexes that would be benefitial?

CREATE TABLE    metric_data (
    id              integer PRIMARY KEY,
    company_id      integer NOT NULL,
    jobs_posted     integer NOT NULL,
    recorded_at     timestamp
);

WITH period_previous AS (
    SELECT      metric_data.company_id,
                SUM(metric_data.jobs_posted) AS jobs_posted
    FROM        metric_data
    WHERE       metric_data.recorded_at BETWEEN CURRENT_DATE - INTERVAL '30 day' * 2 AND CURRENT_DATE - INTERVAL '30 day'
    GROUP BY    metric_data.company_id
), period_current AS (
    SELECT      metric_data.company_id,
                SUM(metric_data.jobs_posted) AS jobs_posted
    FROM        metric_data
    WHERE       metric_data.recorded_at > CURRENT_DATE - INTERVAL '30 day'
    GROUP BY    metric_data.company_id
) 
SELECT          period_current.company_id,
                period_current.jobs_posted AS current_jobs_posted,
                period_current.jobs_posted - period_previous.jobs_posted AS delta
FROM            period_current 
JOIN            period_previous
ON              period_current.company_id = period_previous.company_id

Answer 1

Especially the CTEs might be a problem. So it needs to be rewritten anyway. And while at it, I'd rewrite this to use conditional aggregation. That should get the data in "one run", without two temporary results and a join on them.

SELECT metric_data.company_id,
       sum(CASE
             WHEN metric_data.recorded_at > current_date - INTERVAL '30 day' THEN
               1
             ELSE
               0
           END) current_jobs_posted,
       sum(CASE
             WHEN metric_data.recorded_at > current_date - INTERVAL '30 day' THEN
               1
             ELSE
               -1
           END) delta
       FROM metric_data
       WHERE metric_data.recorded_at >= current_date - INTERVAL '60 day'
       GROUP BY metric_data.company_id;

(You might want to check the comparison operators (>=, >, etc.), if I picked the ones you wanted.)

An index on recorded_at and company_id should support it.

CREATE INDEX metric_data_recorded_at_company_id
             ON metric_data(recorded_at,
                            company_id);

Like that the entire query should be answerable from the index alone, without using the table at all. But maybe you also want to try it with an index just on recorded_at alone.