Postgres 14

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?.

drop table if exists calendar_days;
drop table if exists vacation_days;

create table calendar_days(day_id int, emp_id int, data_year int, data_month int);
create table vacation_days(day_id int, had_fun int);

insert into calendar_days
select row_number() over(), emp_ids, 2019, months
from
generate_series(1,5) emp_ids cross join
generate_series(1,12) months
;

insert into vacation_days
select day_id, 1 from calendar_days where emp_id in (1,2) and data_month=1;

insert into vacation_days
select day_id, 1 from calendar_days where emp_id=4 and data_month=12;

60 rows affected

2 rows affected

1 rows affected

with get_worked_days as (
   select
      day_id,
      emp_id,
      data_year,
      data_month,
      case when v.had_fun is null then 0 else 1 end missed_work
   from calendar_days c
      left join vacation_days v using(day_id)
),
sum_by_quarter as (
   select
      data_year,
      (data_month-1)/3+1 data_quarter,
      emp_id,
      sum(missed_work) vacation_days
   from get_worked_days
   group by
      data_year,
      (data_month-1)/3+1,
      emp_id
)
select
   data_quarter,
   count(*) quarter_count
from sum_by_quarter
where vacation_days=0
group by data_quarter
order by data_quarter
;



data_quarter
quarter_count


1
3

2
5

3
5

4
4


SELECT  (data_month - 1) / 3 + 1 AS data_quarter,
        COUNT(*) AS quarter_count
FROM    calendar_days c
    LEFT JOIN vacation_days v USING(day_id)
GROUP BY (data_month - 1) / 3 + 1,
        ROLLUP(emp_id, data_year)
HAVING  SUM(CASE WHEN v.had_fun IS NULL THEN 0 ELSE 1 END) = 0


data_quarter
quarter_count


1
3

4
3

3
3

4
3

3
3

4
3

3
3

4
3

1
3

2
3

1
3

3
3

2
3

2
3

3
3

2
3

2
3

2
3

3
3

4
3

1
3

1
3

4
3

2
3

3
3

4
3

3
3

1
3

4
3

3
3

2
3

3
3

2
3

2
3

3
15

2
15

data_quarter	quarter_count
1	3
4	3
3	3
4	3
3	3
4	3
3	3
4	3
1	3
2	3
1	3
3	3
2	3
2	3
3	3
2	3
2	3
2	3
3	3
4	3
1	3
1	3
4	3
2	3
3	3
4	3
3	3
1	3
4	3
3	3
2	3
3	3
2	3
2	3
3	15
2	15

data_quarter	quarter_count
1	3
4	3
3	3
4	3
3	3
4	3
3	3
4	3
1	3
2	3
1	3
3	3
2	3
2	3
3	3
2	3
2	3
2	3
3	3
4	3
1	3
1	3
4	3
2	3
3	3
4	3
3	3
1	3
4	3
3	3
2	3
3	3
2	3
2	3
3	15
2	15

data_quarter	quarter_count
1	3
4	3
3	3
4	3
3	3
4	3
3	3
4	3
1	3
2	3
1	3
3	3
2	3
2	3
3	3
2	3
2	3
2	3
3	3
4	3
1	3
1	3
4	3
2	3
3	3
4	3
3	3
1	3
4	3
3	3
2	3
3	3
2	3
2	3
3	15
2	15