Postgres 12

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 IF NOT EXISTS public.t_etf_holdings
(
    trade_date date NOT NULL,
    index_of_ref character varying(25) COLLATE pg_catalog."default" NOT NULL,
    ticker character varying(25) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT t_etf_holdings_pkey PRIMARY KEY (trade_date, index_of_ref, ticker)
);

CREATE TABLE


INSERT INTO t_etf_holdings (trade_date, index_of_ref, ticker)
VALUES('2024-01-06', 'ftse100', 'AAF');
INSERT INTO t_etf_holdings (trade_date, index_of_ref, ticker)
VALUES('2024-01-06', 'ftse100', 'AAL');
INSERT INTO t_etf_holdings (trade_date, index_of_ref, ticker)
VALUES('2024-01-05', 'ftse100', 'AAF');
INSERT INTO t_etf_holdings (trade_date, index_of_ref, ticker)
VALUES('2024-01-05', 'ftse100', 'AAL');
INSERT INTO t_etf_holdings (trade_date, index_of_ref, ticker)
VALUES('2024-01-05', 'ftse100', 'GLEN');
INSERT INTO t_etf_holdings (trade_date, index_of_ref, ticker)
VALUES('2024-01-04', 'ftse100', 'AAF');
INSERT INTO t_etf_holdings (trade_date, index_of_ref, ticker)
VALUES('2024-01-04', 'ftse100', 'AAL');
INSERT INTO t_etf_holdings (trade_date, index_of_ref, ticker)
VALUES('2024-01-04', 'ftse100', 'WPP');

INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


SELECT 
    current_day.trade_date,
    current_day.ticker,
    CASE
        WHEN previous_day.ticker IS NULL THEN '+' -- Ticker added
        WHEN current_day.ticker IS NULL THEN '-' -- Ticker deleted
    END AS change_type
FROM
    t_etf_holdings AS current_day
LEFT JOIN
    t_etf_holdings AS previous_day 
    ON current_day.ticker = previous_day.ticker
    AND current_day.index_of_ref = previous_day.index_of_ref
    AND previous_day.trade_date = (
        SELECT MAX(trade_date)
        FROM t_etf_holdings
        WHERE trade_date < current_day.trade_date
        AND index_of_ref = 'ftse100'
--        AND ticker = current_day.ticker
    )
WHERE
    current_day.index_of_ref = 'ftse100'
AND previous_day.ticker IS NULL
--    AND (
--        (current_day.ticker IS NOT NULL AND previous_day.ticker IS NULL)
--        OR 
--      (current_day.ticker IS NULL AND previous_day.ticker IS NOT NULL)
--    )
ORDER BY
    current_day.trade_date DESC, current_day.ticker;


trade_date
ticker
change_type


2024-01-05
GLEN
+

2024-01-04
AAF
+

2024-01-04
AAL
+

2024-01-04
WPP
+



SELECT 4