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 areas (
id int not null primary key,
location text not null unique
);
create table happenings (
id int generated always as identity primary key,
primary_area_id int not null references areas(id),
started_at date not null
);
insert into areas values (1, 'Location A'), (2, 'Location B');
insert into happenings (primary_area_id, started_at) values
(1, '2023-02-01'), (1, '2023-02-08'), (1, '2023-02-15'), (1, '2023-02-22'),
(2, '2023-02-01'), (2, '2023-02-08'), (2, '2023-02-09'), (2, '2023-02-22');
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 8
with parms as ( -- CTE for your input parameters
select '2023-02-01'::date as start_date, '2023-02-28'::date as end_date
), area_weeks as( -- Create pigeonholes for all weeks
select gs.bow, count(*) over () as num_weeks
from parms
cross join lateral
generate_series(
date_trunc('week', start_date),
date_trunc('week', end_date),
interval '7 days'
) as gs(bow)
), collapse_weeks as ( -- Keep only whether an area had any event
select distinct primary_area_id,
date_trunc('week', started_at) as bow -- "beginning of week"
from happenings
)
select a.location,
count(h.bow) as weeks_with_happenings,
max(aw.num_weeks) as num_weeks,
1.0 * avg((h.bow is not null)::int) as result
-- Use the average of a boolean result cast to int to get a percentage
-- without having to calculate counts or guard against
-- division by zero. Multiply by 1.0 to force float arithmetic.
from areas a
cross join area_weeks aw -- expand week pigeonholes to weeks * areas
left join collapse_weeks h -- happening exists or does not
on h.bow = aw.bow and h.primary_area_id = a.id
group by a.location;
| location | weeks_with_happenings | num_weeks | result |
|---|---|---|---|
| Location A | 4 | 5 | 0.800000000000000000000 |
| Location B | 3 | 5 | 0.600000000000000000000 |
SELECT 2