By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE influencer_follower_daily (influencer_id int, date date, followers int);
INSERT INTO influencer_follower_daily VALUES
(1, '2020-05-29', 7361)
, (1, '2020-05-28', 7234)
, (2, '2020-05-29', 82)
, (2, '2020-05-28', 85)
, (3, '2020-05-29', 3434)
, (3, '2020-05-28', 2988)
, (3, '2020-05-27', 2765);
7 rows affected
-- with array_agg() once
SELECT influencer_id, arr[array_upper(arr, 1)] - arr[1]
FROM (
SELECT influencer_id, array_agg(followers) AS arr
FROM (
SELECT influencer_id, followers
FROM influencer_follower_daily
WHERE date >= '2020-05-23'
AND date < '2020-05-30'
ORDER BY influencer_id, date
) sub1
GROUP BY influencer_id
) sub2;
| influencer_id | ?column? |
|---|---|
| 1 | 127 |
| 2 | -3 |
| 3 | 669 |
-- with DISTNCT ON and last_value()
SELECT DISTINCT ON (influencer_id)
influencer_id
, last_value(followers) OVER (PARTITION BY influencer_id ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - followers AS growth
FROM influencer_follower_daily
WHERE date >= '2020-05-23'
AND date < '2020-05-30'
ORDER BY influencer_id, date;
| influencer_id | growth |
|---|---|
| 1 | 127 |
| 2 | -3 |
| 3 | 669 |
CREATE OR REPLACE FUNCTION f_growth(anyarray)
RETURNS anyelement LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE AS
'SELECT $1[array_upper($1, 1)] - $1[1]';
CREATE OR REPLACE AGGREGATE growth(anyelement) (
SFUNC = array_append
, STYPE = anyarray
, FINALFUNC = f_growth
, PARALLEL = SAFE
);
SELECT influencer_id, growth(followers)
FROM (
SELECT influencer_id, followers
FROM influencer_follower_daily
WHERE date >= '2020-05-23'
AND date < '2020-05-30'
ORDER BY influencer_id, date
) z
GROUP BY influencer_id
ORDER BY influencer_id;
| influencer_id | growth |
|---|---|
| 1 | 127 |
| 2 | -3 |
| 3 | 669 |
SELECT influencer_id, growth(followers ORDER BY date)
FROM influencer_follower_daily
WHERE date >= '2020-05-23'
AND date < '2020-05-30'
GROUP BY 1
ORDER BY 1;
| influencer_id | growth |
|---|---|
| 1 | 127 |
| 2 | -3 |
| 3 | 669 |