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 survey (
responder text NOT NULL
, date date NOT NULL
, response text
);
INSERT INTO survey VALUES
('mary', '2021-07-15', 'yes')
, ('lucy', '2021-07-01', 'no')
, ('mary', '2021-06-05', 'maybe')
, ('lucy', '2021-06-05', 'yes')
, ('noyes', '2021-06-01', 'yes')
, ('noyes', '2021-06-02', 'no')
, ('noyes', '2021-06-03', 'yes')
;
CREATE TABLE
INSERT 0 7
SELECT count(DISTINCT responder)
FROM survey s
WHERE response = 'yes'
AND EXISTS (
SELECT FROM survey s1
WHERE s1.responder = s.responder
AND s1.response = 'no'
AND s1.date < s.date
);
| count |
|---|
| 1 |
SELECT 1
SELECT count(*)
FROM (
SELECT FROM survey
GROUP BY responder
HAVING min(date) FILTER (WHERE response = 'no')
< max(date) FILTER (WHERE response = 'yes')
) sub;
| count |
|---|
| 1 |
SELECT 1
-- Gordon's query: 2x min can fail:
SELECT count(*)
FROM (SELECT min(date) filter (WHERE response = 'no') AS no_date,
min(date) filter (WHERE response = 'yes') AS yes_date
FROM survey
GROUP BY responder
) r
WHERE no_date < yes_date;
| count |
|---|
| 0 |
SELECT 1