Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE volunteer_presence (id integer, volunteer_id integer, minutes integer);
INSERT INTO volunteer_presence (id, volunteer_id, minutes) values (1, 333, 400);
INSERT INTO volunteer_presence (id, volunteer_id, minutes) values (2, 444, 90);
INSERT INTO volunteer_presence (id, volunteer_id, minutes) values (3, 555, 80);

SELECT * FROM volunteer_presence;
1 rows affected

1 rows affected

1 rows affected



id
volunteer_id
minutes


1
333
400

2
444
90

3
555
80


CREATE TABLE logged_actions (id  integer, presence_id integer, section varchar);
INSERT INTO logged_actions (id, presence_id, section) values (10, 1, 'Cats');
INSERT INTO logged_actions (id, presence_id, section) values (11, 1, 'Dogs');
INSERT INTO logged_actions (id, presence_id, section) values (13, 1, 'Cats');
INSERT INTO logged_actions (id, presence_id, section) values (14, 1, 'Cats');
INSERT INTO logged_actions (id, presence_id, section) values (15, 1, 'Front');
INSERT INTO logged_actions (id, presence_id, section) values (16, 2, 'Dogs');

SELECT * FROM logged_actions
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected



id
presence_id
section


10
1
Cats

11
1
Dogs

13
1
Cats

14
1
Cats

15
1
Front

16
2
Dogs


SELECT 
    *,
    COUNT(*) OVER (PARTITION BY presence_id)
FROM logged_actions la


id
presence_id
section
count


10
1
Cats
5

11
1
Dogs
5

13
1
Cats
5

14
1
Cats
5

15
1
Front
5

16
2
Dogs
1


SELECT
    presence_id,
    section,
    total,
    COUNT(*)
FROM (
    SELECT 
        *,
        COUNT(*) OVER (PARTITION BY presence_id) as total
    FROM logged_actions
) la
GROUP BY presence_id, section, total


presence_id
section
total
count


1
Cats
5
3

1
Dogs
5
1

2
Dogs
1
1

1
Front
5
1


SELECT
    la.presence_id,
    la.section,
    la.total,
    vp.minutes,
    COUNT(*)
FROM (
    SELECT 
        *,
        COUNT(*) OVER (PARTITION BY presence_id) as total
    FROM logged_actions
) la
JOIN volunteer_presence vp ON vp.id = la.presence_id
GROUP BY la.presence_id, la.section, la.total, vp.minutes


presence_id
section
total
minutes
count


1
Dogs
5
400
1

2
Dogs
1
90
1

1
Front
5
400
1

1
Cats
5
400
3


SELECT
    la.presence_id,
    la.section,
    vp.minutes * COUNT(*) / total
FROM (
    SELECT 
        *,
        COUNT(*) OVER (PARTITION BY presence_id) as total
    FROM logged_actions
) la
JOIN volunteer_presence vp ON vp.id = la.presence_id
GROUP BY la.presence_id, la.section, la.total, vp.minutes


presence_id
section
?column?


1
Dogs
80

2
Dogs
90

1
Front
80

1
Cats
240