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