SQL: How to query the average of monthly sum, when some months don't have record?

Question

TL;WR: How to query the average of monthly sum, when some months don't have record (so should be 0)?

Background

My kids are reporting daily how long they have done chores (in a PostgreSQL database). My dataset then looks like this:

date,user,duration

2020-01-01,Alice,120
2020-01-02,Bob,30
2020-01-03,Charlie,10
2020-01-23,Charlie,10

2020-02-03,Charlie,10
2020-02-23,Charlie,10

2020-03-02,Bob,30
2020-03-03,Charlie,10
2020-03-23,Charlie,10

I want to know how much, on average, do they do per month. Concretely, the result I want is:

Alice: 40 =(120+0+0)÷3
Bob: 20 =(30+0+30)÷3
Charlie: 20 =([10+10]+[10+10]+[10+10])÷3

Problem

On some months, I don't have record for some users (e.g., Alice in February and March). Thus, running the following nested query doesn't return the result I want; indeed, this doesn't take in consideration that, because there is no record for these months, Alice's contribution in February and March should be 0 (here the average is wrongly computed as 120).

-- this does not work
SELECT
    "user",
    round(avg(monthly_duration)) as avg_monthly_sum
FROM (
    SELECT
        date_trunc('month', date),
        "user",
        sum(duration) as monthly_duration
    FROM
        public.chores_record
    GROUP BY
        date_trunc('month', date),
        "user"
) AS monthly_sum
GROUP BY
    "user"
;
-- Doesn't return what I want:
--
-- "unique_user","avg_monthly_sum"
-- "Alice",120
-- "Bob",30
-- "Charlie",20

Thus, I have built a quite cumbersome query as follows:

List the unique months,
List the unique users,
Generate the months×users combinations,
Add the monthly sum from the original data,
Get the average of monthly sum (assuming 'null' = 0).

SELECT
    unique_user,
    round(avg(COALESCE(monthly_duration, 0))) -- COALESCE transforms 'null' into 0
FROM (
    -- monthly duration with 'null' if no record for that user×month
    SELECT
        month_user_combinations.month,
        month_user_combinations.unique_user,
        monthly_duration.monthly_duration
    FROM
    (
        (
            -- all months×users combinations
            SELECT
                month,
                unique_user
            FROM (
                (
                    -- list of unique months
                    SELECT DISTINCT
                        date_trunc('month', date) as month
                    FROM
                        public.chores_record
                ) AS unique_months
                CROSS JOIN
                (
                    -- list of unique users
                    SELECT DISTINCT
                        "user" as "unique_user"
                    FROM
                        public.chores_record
                ) AS unique_users
            )
        ) AS month_user_combinations
        LEFT OUTER JOIN
        (
            -- monthly duration for existing month×user combination only
            SELECT
                date_trunc('month', date) as month,
                "user",
                sum(duration) as monthly_duration
            FROM
                public.chores_record
            GROUP BY
                date_trunc('month', date),
                "user"
        ) AS monthly_duration
        ON (
            month_user_combinations.month = monthly_duration.month
            AND
            month_user_combinations.unique_user = monthly_duration.user
        )
    )
) AS monthly_duration_for_all_combinations
GROUP BY
    unique_user
;

This query works, but is quite bulky.

Question

How to query the average of monthly sum more elegantly than above, taking “no record ⇒ monthly sum = 0” into account?

Note: it is safe to assume that I want to compute the average on the months that have at least one record only (i.e. it's normal not to consider December or April here.)

MWE

CREATE TABLE public.chores_record
(
    date date NOT NULL,
    "user" text NOT NULL,
    duration integer NOT NULL,
    PRIMARY KEY (date, "user")
);

INSERT INTO
    public.chores_record(date, "user", duration)
VALUES
    ('2020-01-01','Alice',120),
    ('2020-01-02','Bob',30),
    ('2020-01-03','Charlie',10),
    ('2020-01-23','Charlie',10),
    ('2020-02-03','Charlie',10),
    ('2020-02-23','Charlie',10),
    ('2020-03-02','Bob',30),
    ('2020-03-03','Charlie',10),
    ('2020-03-23','Charlie',10)
;

user_905902 · Answer

You could use a CTE to construct a calendar-table:

-- EXPLAIN
WITH cal AS ( -- The unique months
        SELECT DISTINCT date_trunc('mon', zdate) AS tick
        FROM chores_record
        )
, cnt AS (      -- the number of months (a scalar)
        SELECT COUNT(*) AS nmonth
        FROM cal
        )
SELECT
        x.zuser
        , SUM(x.duration) AS tot_duration
        , SUM(x.duration) / SUM(c.nmonth) AS Averarage_month -- this is ugly ...
FROM cal t
JOIN cnt c ON true -- This is ugly
LEFT JOIN chores_record x ON date_trunc('mon', x.zdate) = t.tick
GROUP BY x.zuser
        ;

user_593144 · Answer

You need two additional datasets for this: the list of kids and the list of months:

with
    k as (/* list of kids */
        select distinct "user" from chores_record),
    m as (/* list of months in format "yyyy-mm-01" */
        select distinct date_trunc('month', "date") as "month" from chores_record),
    d as (/* sums by moths and kids */
        select
            date_trunc('month', "date") as "month",
            "user",
            sum(duration) as duration
        from chores_record
        group by 1, 2)
select
    m."month",
    k."user",
    coalesce(d.duration, 0) as duration
from
    k cross join m left join d on (k."user" = d."user" and m."month" = d."month")
order by "month", "user";

┌────────────────────────┬─────────┬──────────┐
│         month          │  user   │ duration │
├────────────────────────┼─────────┼──────────┤
│ 2020-01-01 00:00:00+02 │ Alice   │      120 │
│ 2020-01-01 00:00:00+02 │ Bob     │       30 │
│ 2020-01-01 00:00:00+02 │ Charlie │       20 │
│ 2020-02-01 00:00:00+02 │ Alice   │        0 │
│ 2020-02-01 00:00:00+02 │ Bob     │        0 │
│ 2020-02-01 00:00:00+02 │ Charlie │       20 │
│ 2020-03-01 00:00:00+02 │ Alice   │        0 │
│ 2020-03-01 00:00:00+02 │ Bob     │       30 │
│ 2020-03-01 00:00:00+02 │ Charlie │       20 │
└────────────────────────┴─────────┴──────────┘

And the last step is the calculation of the averages:

with
    ...
select
    k."user",
    avg(coalesce(d.duration, 0)) as duration
from
    k cross join m left join d on (k."user" = d."user" and m."month" = d."month")
group by k."user"
order by k."user";

┌─────────┬─────────────────────┐
│  user   │      duration       │
├─────────┼─────────────────────┤
│ Alice   │ 40.0000000000000000 │
│ Bob     │ 20.0000000000000000 │
│ Charlie │ 20.0000000000000000 │
└─────────┴─────────────────────┘

user_10676716 · Answer

In Postgres, I would recommend generate_series() to construct the calendar table, then aggregation. The upside is that it works even if there are months where no user was active.

select u."user", avg(coalesce(c.duration, 0)) avg_duration 
from (
    select generate_series(date_trunc('month', min(date)), date_trunc('month', max(date)), '1 month') as dt
    from chores_record
) d
cross join (select distinct "user" from chores_record) u
left join (
    select "user", date_trunc('month', date) as dt, sum(duration) as duration
    from chores_record c 
    group by "user", date_trunc('month', date)
) c on c."user" = u."user" and c.dt = d.dt
group by u."user"

generate_series() generates all starts of months between the earliest and latest date in the table. Then we cross join that with the list of distinct users (in a real life situation, you would probably have a referential table to store the users, that you would use instead). Then, we aggregate the original table by user and month, and left join it with the user/month combinations. The final step is outer aggregation.

user_5845802 · Answer

As the use case was smaller (not millions of rows), a simple approach is to separately find

the total hours per user and
total distinct months across all users

join the 2 to get the answer

select "user", totalHours/monthCount from
(select "user", sum(duration) totalHours from chores_record group by "user") as a,
(select count(distinct(to_char(date, 'YYYYMM'))) monthCount from chores_record) as b
;

Alice,40
Bob,20
Charlie,20

SQL: How to query the average of monthly sum, when some months don't have record?

Background

Problem

Question

MWE

4 Answers