SQL - Average x per y, per z

Question

I have a data set of two columns, an ID and a datetime

I am trying to get an average count of hours per ID each month.

So far I have managed to use this code to get me the count of hours for an individual day:

SELECT
    id,
    DATE(created),
    DATE_PART(hour, created)::int AS "hour",
    --Counts 15 minute windows of activity
    FLOOR(DATE_PART(minute, created) / 15) AS "15 mins",
    (COUNT(*) OVER (PARTITION BY id,
            DATE(created)) / 4.0) AS "Online Hours"
FROM
--The temporary table I pulled the data into
    #tempres
GROUP BY
    id,
    DATE(created),
    DATE_PART(hour, created),
    FLOOR(DATE_PART(minute, created) / 15)
ORDER BY
    id DESC,
    DATE(created),
    "hour" ASC,
    "15 mins" ASC;

But I've not been able to figure out how to apply AVG over the windowed function to get the monthly result.

This Mockaroo link should produce the same kind of data I'm using.

I am aware that this is probably not the most streamlined starting point, so I'm very open to any other ideas!

score 0 · Accepted Answer · answered Oct 18 '21 at 15:26

OK, so I have come up with a mostly functional query.

By chucking the old query into a CTE I'm able to run the averaging aggregate function on it over a monthly time period.

Still very open to cleaner options, but I thought I would put what I had here.

WITH cte AS (
    SELECT
        id,
        MAX(created) AS "created",
        DATE_PART(month,
            created) AS "month",
        DATE(created) AS "day",
        DATE_PART(hour,
            created)::int AS "hour",
        FLOOR(DATE_PART(minute,
                created) / 15) AS "15_mins",
        COUNT(id),
        (COUNT(*) OVER (PARTITION BY id,
                DATE(created)) / 4.0) AS "online_hours"
    FROM
        #tempres
    GROUP BY
        id,
        DATE_PART(month,
        created),
        DATE(created),
        DATE_PART(hour,
        created),
        FLOOR(DATE_PART(minute,
        created) / 15)
        ORDER BY
            id DESC,
            DATE(created),
            "hour" ASC,
            "15_mins" ASC
) SELECT
    id,
    DATE_PART(year, cte.created) AS "Year",
    cte.month,
    ROUND(AVG(online_hours), 2) AS "Average Hours"
FROM
    cte
GROUP BY
    id,
    DATE_PART(year, cte.created),
    cte.month
ORDER BY
    id,
    DATE_PART(year, cte.created),
    cte.month;

SQL - Average x per y, per z

1 Answers1