Query aggregation of grouped data in date range

SQL++
1

Hello i have some task system and i’m trying to get data to know average age of their open status. Something like:
for each day in range (2020-01-01 - 2020-01-02) give average time from createdAt to closedAt.

Example data in database:
[{
createdAt: “2020-01-01 00:00:00”, //formats of dates are timestamps - im using this format just for readability
closedAt: “2020-01-02 12:00:00”
},
{
createdAt: “2020-01-01 :00:00:00”,
closedAt: “2020-01-03 :24:00:00”
}]

as result i need something like
[
“2020-01-01”: “24 (in hours)” // 48hours (2* 24) / 2 - 2 items for this day
“2020-01-02”: “42” // 84 (48+36) / 2
“2020-01-03”: “72” // 72 / 1
]

I hope it is understandable. Do anyone know how to make efficient query? It is easy to make 3 queries for each day but i would like to make it in single query. Date range can be months. Thanks everyone.

2

Requires CB 6.5.0 EE

INSERT INTO default VALUES ("f01",{ "createdAt": "2020-01-01T00:00:00", "closedAt": "2020-01-02T12:00:00" });
INSERT INTO default VALUES ("f02", { "createdAt": "2020-01-01T00:00:00", "closedAt": "2020-01-03T23:59:00" });

SELECT SUM(t.s)/COUNT (DISTINCT t.id) AS avg, t.date
FROM ( SELECT  META(d).id, date, SUM(COUNT(dr)) OVER ( PARTITION BY META(d).id ORDER BY date) AS s
       FROM default AS d
       UNNEST DATE_RANGE_STR(d.createdAt, d.closedAt, "hour") AS dr
       LET date = DATE_FORMAT_STR(dr,"1111-11-11")
       WHERE d.createdAt IS NOT NULL
       GROUP BY  META(d).id, date) AS t
GROUP BY t.date;

OR

SELECT SUM(t.s)/COUNT (DISTINCT t.id) AS avg, t.date
FROM (SELECT META(d).id,
             date,
             SUM(DATE_DIFF_STR(date,d.createdAt,"day")*24+(24-DATE_PART_STR(d.createdAt,"hour"))-CASE WHEN DATE_FORMAT_STR(d.closedAt,"1111-11-11") == date THEN 24-(DATE_PART_STR(d.closedAt,"hour") + CASE WHEN DATE_PART_STR(d.closedAt,"minute") > 0 OR DATE_PART_STR(d.closedAt,"second") THEN 1 ELSE 0 END) ELSE 0 END) AS s
       FROM default AS d
       UNNEST DATE_RANGE_STR(d.createdAt, d.closedAt, "day") AS dr
       LET date = DATE_FORMAT_STR(dr,"1111-11-11")
       WHERE d.createdAt IS NOT NULL
       GROUP BY META(d).id, date) AS t
GROUP BY t.date;

OR

SELECT
     date,
     SUM ( (24-DATE_PART_STR(d.createdAt,"hour")) +
            (DATE_DIFF_STR(date, d.createdAt,"day") * 24) -
            CASE WHEN DATE_FORMAT_STR(d.closedAt,"1111-11-11") == date
                 THEN 24 - (DATE_PART_STR(d.closedAt,"hour") +
                      CASE WHEN DATE_PART_STR(d.closedAt,"minute") > 0 OR DATE_PART_STR(d.closedAt,"second")
                           THEN 1 ELSE 0
                      END)
                  ELSE 0
            END) / COUNT (DISTINCT META(d).id) AS avg
FROM default AS d
UNNEST DATE_RANGE_STR(d.createdAt, d.closedAt, "day") AS dr
LET date = DATE_FORMAT_STR(dr,"1111-11-11")
WHERE d.createdAt IS NOT NULL
GROUP BY date
ORDER BY date;