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 table_a
("name" varchar(6), "asof_yrmo" int)
;
INSERT INTO table_a VALUES
('Will', 202101),
('James', 202101),
('Samuel', 202101),
('John', 202101),
('George', 202101),
('Will', 202012),
('James', 202012),
('Samuel', 202012),
('John', 202012),
('George', 202012);
SELECT * FROM table_a;
CREATE TABLE table_b
("name" varchar(6), "comp" varchar(1), "asof_yrmo" int)
;
INSERT INTO table_b VALUES
('Will', 'Y', 202101),
('James', 'Y', 202101),
('Samuel', 'Y', 202101),
('John', 'N', 202101),
('George', 'N', 202101),
('Will', 'N', 202012),
('James', 'N', 202012),
('Samuel', 'Y', 202012),
('John', 'Y', 202012),
('George', 'Y', 202012);
SELECT * FROM table_b;
10 rows affected
| name | asof_yrmo |
|---|---|
| Will | 202101 |
| James | 202101 |
| Samuel | 202101 |
| John | 202101 |
| George | 202101 |
| Will | 202012 |
| James | 202012 |
| Samuel | 202012 |
| John | 202012 |
| George | 202012 |
10 rows affected
| name | comp | asof_yrmo |
|---|---|---|
| Will | Y | 202101 |
| James | Y | 202101 |
| Samuel | Y | 202101 |
| John | N | 202101 |
| George | N | 202101 |
| Will | N | 202012 |
| James | N | 202012 |
| Samuel | Y | 202012 |
| John | Y | 202012 |
| George | Y | 202012 |
SELECT
*
FROM table_a a
JOIN table_b b ON a.name = b.name AND a.asof_yrmo = b.asof_yrmo
| name | asof_yrmo | name | comp | asof_yrmo |
|---|---|---|---|---|
| George | 202012 | George | Y | 202012 |
| George | 202101 | George | N | 202101 |
| James | 202012 | James | N | 202012 |
| James | 202101 | James | Y | 202101 |
| John | 202012 | John | Y | 202012 |
| John | 202101 | John | N | 202101 |
| Samuel | 202012 | Samuel | Y | 202012 |
| Samuel | 202101 | Samuel | Y | 202101 |
| Will | 202012 | Will | N | 202012 |
| Will | 202101 | Will | Y | 202101 |
SELECT
a.name,
MAX(a.asof_yrmo) as asof_yrmo,
MAX(comp) FILTER (WHERE a.asof_yrmo = '202101') as comp,
MAX(comp) FILTER (WHERE a.asof_yrmo = '202012') as comp_from_prev_two_years
FROM table_a a
JOIN table_b b ON a.name = b.name AND a.asof_yrmo = b.asof_yrmo
GROUP BY a.name
| name | asof_yrmo | comp | comp_from_prev_two_years |
|---|---|---|---|
| George | 202101 | N | Y |
| James | 202101 | Y | N |
| John | 202101 | N | Y |
| Samuel | 202101 | Y | Y |
| Will | 202101 | Y | N |