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 schema uniswap_v2;
create schema uniswap;
create table uniswap_v2."Factory_evt_PairCreated" (
evt_block_time timestamp
);
create table uniswap."Factory_evt_NewExchange" (
evt_block_time timestamp
);
insert into uniswap."Factory_evt_NewExchange" values ('2020-01-01'), ('2020-02-02'), ('2020-03-03');
insert into uniswap_v2."Factory_evt_PairCreated" values ('2020-01-01'), ('2021-12-12'), ('2021-11-11');
3 rows affected
3 rows affected
SELECT
evt_block_week,
COUNT(*) filter (
WHERE
uniswap_version = 'v1'
) as v1_pairs,
COUNT(*) filter (
WHERE
uniswap_version = 'v2'
) as v2_pairs
FROM
(
SELECT
'v2' as uniswap_version,
date_trunc('week', evt_block_time) as evt_block_week
FROM
uniswap_v2."Factory_evt_PairCreated"
UNION ALL
SELECT
'v1' as uniswap_version,
date_trunc('week', evt_block_time) as evt_block_week
FROM
uniswap."Factory_evt_NewExchange"
) as creations
group by evt_block_week
order by evt_block_week
| evt_block_week | v1_pairs | v2_pairs |
|---|---|---|
| 2019-12-30 00:00:00 | 1 | 1 |
| 2020-01-27 00:00:00 | 1 | 0 |
| 2020-03-02 00:00:00 | 1 | 0 |
| 2021-11-08 00:00:00 | 0 | 1 |
| 2021-12-06 00:00:00 | 0 | 1 |
SELECT
weeks.week,
COUNT(*) filter (
WHERE
uniswap_version = 'v1'
) as v1_pairs,
COUNT(*) filter (
WHERE
uniswap_version = 'v2'
) as v2_pairs
from (
select
generate_series(
date_trunc('week', '2020-01-01'::date), date_trunc('week', '2020-12-31'::date), '1 week'
) as week
) as weeks
left join
(
SELECT
'v2' as uniswap_version,
date_trunc('week', evt_block_time) as evt_block_week
FROM
uniswap_v2."Factory_evt_PairCreated"
UNION ALL
SELECT
'v1' as uniswap_version,
date_trunc('week', evt_block_time) as evt_block_week
FROM
uniswap."Factory_evt_NewExchange"
) as creations on weeks.week = evt_block_week
group by week
order by week
| week | v1_pairs | v2_pairs |
|---|---|---|
| 2019-12-30 00:00:00+00 | 1 | 1 |
| 2020-01-06 00:00:00+00 | 0 | 0 |
| 2020-01-13 00:00:00+00 | 0 | 0 |
| 2020-01-20 00:00:00+00 | 0 | 0 |
| 2020-01-27 00:00:00+00 | 1 | 0 |
| 2020-02-03 00:00:00+00 | 0 | 0 |
| 2020-02-10 00:00:00+00 | 0 | 0 |
| 2020-02-17 00:00:00+00 | 0 | 0 |
| 2020-02-24 00:00:00+00 | 0 | 0 |
| 2020-03-02 00:00:00+00 | 1 | 0 |
| 2020-03-09 00:00:00+00 | 0 | 0 |
| 2020-03-16 00:00:00+00 | 0 | 0 |
| 2020-03-23 00:00:00+00 | 0 | 0 |
| 2020-03-30 00:00:00+01 | 0 | 0 |
| 2020-04-06 00:00:00+01 | 0 | 0 |
| 2020-04-13 00:00:00+01 | 0 | 0 |
| 2020-04-20 00:00:00+01 | 0 | 0 |
| 2020-04-27 00:00:00+01 | 0 | 0 |
| 2020-05-04 00:00:00+01 | 0 | 0 |
| 2020-05-11 00:00:00+01 | 0 | 0 |
| 2020-05-18 00:00:00+01 | 0 | 0 |
| 2020-05-25 00:00:00+01 | 0 | 0 |
| 2020-06-01 00:00:00+01 | 0 | 0 |
| 2020-06-08 00:00:00+01 | 0 | 0 |
| 2020-06-15 00:00:00+01 | 0 | 0 |
| 2020-06-22 00:00:00+01 | 0 | 0 |
| 2020-06-29 00:00:00+01 | 0 | 0 |
| 2020-07-06 00:00:00+01 | 0 | 0 |
| 2020-07-13 00:00:00+01 | 0 | 0 |
| 2020-07-20 00:00:00+01 | 0 | 0 |
| 2020-07-27 00:00:00+01 | 0 | 0 |
| 2020-08-03 00:00:00+01 | 0 | 0 |
| 2020-08-10 00:00:00+01 | 0 | 0 |
| 2020-08-17 00:00:00+01 | 0 | 0 |
| 2020-08-24 00:00:00+01 | 0 | 0 |
| 2020-08-31 00:00:00+01 | 0 | 0 |
| 2020-09-07 00:00:00+01 | 0 | 0 |
| 2020-09-14 00:00:00+01 | 0 | 0 |
| 2020-09-21 00:00:00+01 | 0 | 0 |
| 2020-09-28 00:00:00+01 | 0 | 0 |
| 2020-10-05 00:00:00+01 | 0 | 0 |
| 2020-10-12 00:00:00+01 | 0 | 0 |
| 2020-10-19 00:00:00+01 | 0 | 0 |
| 2020-10-26 00:00:00+00 | 0 | 0 |
| 2020-11-02 00:00:00+00 | 0 | 0 |
| 2020-11-09 00:00:00+00 | 0 | 0 |
| 2020-11-16 00:00:00+00 | 0 | 0 |
| 2020-11-23 00:00:00+00 | 0 | 0 |
| 2020-11-30 00:00:00+00 | 0 | 0 |
| 2020-12-07 00:00:00+00 | 0 | 0 |
| 2020-12-14 00:00:00+00 | 0 | 0 |
| 2020-12-21 00:00:00+00 | 0 | 0 |
| 2020-12-28 00:00:00+00 | 0 | 0 |