Revision 79895e3a-7d45-4870-b4e8-21d46ba9b54e - Stack Overflow

Please help me write a query. I have a table with data on user applications.
It contains several fields:
`employee_id text`, `date date`, `app_info jsonb`.
The info `field app_info` contains the following code:

```json
[
 {
 "apps": [
 {
 "date": "2025年07月31日",
 "app_name": "Postman",
 "type_name": "Native",
 "domain_site": "",
 "employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
 "count_seconds": 155.334
 }
 ],
 "granula_end": "14:50:00",
 "granula_start": "14:45:00"
 },
 {
 "apps": [
 {
 "date": "2025年07月31日",
 "app_name": "CLion",
 "type_name": "Native",
 "domain_site": "",
 "employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
 "count_seconds": 282.565
 },
 {
 "date": "2025年07月31日",
 "app_name": "WinSCP: SFTP, FTP, WebDAV, S3 and SCP client",
 "type_name": "Native",
 "domain_site": "",
 "employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
 "count_seconds": 459.091
 },
 {
 "date": "2025年07月31日",
 "app_name": "Проводник",
 "type_name": "Native",
 "domain_site": "",
 "employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
 "count_seconds": 474.966
 }
 ],
 "granula_end": "14:55:00",
 "granula_start": "14:50:00"
 },
 {
 "apps": null,
 "granula_end": "15:00:00",
 "granula_start": "14:55:00"
 },
 {
 "apps": null,
 "granula_end": "15:05:00",
 "granula_start": "15:00:00"
 },
 {
 "apps": null,
 "granula_end": "15:10:00",
 "granula_start": "15:05:00"
 },
 {
 "apps": null,
 "granula_end": "15:15:00",
 "granula_start": "15:10:00"
 },
 {
 "apps": null,
 "granula_end": "15:20:00",
 "granula_start": "15:15:00"
 },
 {
 "apps": null,
 "granula_end": "15:25:00",
 "granula_start": "15:20:00"
 },
 {
 "apps": null,
 "granula_end": "15:30:00",
 "granula_start": "15:25:00"
 },
 {
 "apps": null,
 "granula_end": "15:35:00",
 "granula_start": "15:30:00"
 },
 {
 "apps": null,
 "granula_end": "15:40:00",
 "granula_start": "15:35:00"
 },
 {
 "apps": null,
 "granula_end": "15:45:00",
 "granula_start": "15:40:00"
 },
 {
 "apps": null,
 "granula_end": "15:50:00",
 "granula_start": "15:45:00"
 },
 {
 "apps": null,
 "granula_end": "15:55:00",
 "granula_start": "15:50:00"
 },
 {
 "apps": null,
 "granula_end": "16:00:00",
 "granula_start": "15:55:00"
 },
 {
 "apps": null,
 "granula_end": "16:05:00",
 "granula_start": "16:00:00"
 },
 {
 "apps": null,
 "granula_end": "16:10:00",
 "granula_start": "16:05:00"
 },
 {
 "apps": [
 {
 "date": "2025年07月31日",
 "app_name": "CLion",
 "type_name": "Native",
 "domain_site": "",
 "employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
 "count_seconds": 243.496
 },
 {
 "date": "2025年07月31日",
 "app_name": "Postman",
 "type_name": "Native",
 "domain_site": "",
 "employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
 "count_seconds": 319.902
 }
 ],
 "granula_end": "16:15:00",
 "granula_start": "16:10:00"
 },
 {
 "apps": null,
 "granula_end": "16:20:00",
 "granula_start": "16:15:00"
 },
 {
 "apps": null,
 "granula_end": "16:25:00",
 "granula_start": "16:20:00"
 },
 {
 "apps": null,
 "granula_end": "16:30:00",
 "granula_start": "16:25:00"
 },
 {
 "apps": null,
 "granula_end": "16:35:00",
 "granula_start": "16:30:00"
 },
 {
 "apps": null,
 "granula_end": "16:40:00",
 "granula_start": "16:35:00"
 },
 {
 "apps": null,
 "granula_end": "16:45:00",
 "granula_start": "16:40:00"
 },
 {
 "apps": null,
 "granula_end": "16:50:00",
 "granula_start": "16:45:00"
 },
 {
 "apps": null,
 "granula_end": "16:55:00",
 "granula_start": "16:50:00"
 },
 {
 "apps": [
 {
 "date": "2025年07月31日",
 "app_name": "CLion",
 "type_name": "Native",
 "domain_site": "",
 "employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
 "count_seconds": 192.897
 },
 {
 "date": "2025年07月31日",
 "app_name": "Postman",
 "type_name": "Native",
 "domain_site": "",
 "employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
 "count_seconds": 111.307
 },
 {
 "date": "2025年07月31日",
 "app_name": "Проводник",
 "type_name": "Native",
 "domain_site": "",
 "employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
 "count_seconds": 144.277
 }
 ],
 "granula_end": "17:00:00",
 "granula_start": "16:55:00"
 },
 {
 "apps": null,
 "granula_end": "17:05:00",
 "granula_start": "17:00:00"
 },
 {
 "apps": null,
 "granula_end": "17:10:00",
 "granula_start": "17:05:00"
 },
 {
 "apps": null,
 "granula_end": "17:15:00",
 "granula_start": "17:10:00"
 },
 {
 "apps": null,
 "granula_end": "17:20:00",
 "granula_start": "17:15:00"
 },
 {
 "apps": null,
 "granula_end": "17:25:00",
 "granula_start": "17:20:00"
 },
 {
 "apps": null,
 "granula_end": "17:30:00",
 "granula_start": "17:25:00"
 },
 {
 "apps": null,
 "granula_end": "17:35:00",
 "granula_start": "17:30:00"
 },
 {
 "apps": [
 {
 "date": "2025年07月31日",
 "app_name": "CLion",
 "type_name": "Native",
 "domain_site": "",
 "employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
 "count_seconds": 66.195
 },
 {
 "date": "2025年07月31日",
 "app_name": "Postman",
 "type_name": "Native",
 "domain_site": "",
 "employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
 "count_seconds": 45.276
 }
 ],
 "granula_end": "17:40:00",
 "granula_start": "17:35:00"
 },
 {
 "apps": null,
 "granula_end": "17:45:00",
 "granula_start": "17:40:00"
 },
 {
 "apps": null,
 "granula_end": "17:50:00",
 "granula_start": "17:45:00"
 },
 {
 "apps": [
 {
 "date": "2025年07月31日",
 "app_name": "CLion",
 "type_name": "Native",
 "domain_site": "",
 "employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
 "count_seconds": 3.86
 },
 {
 "date": "2025年07月31日",
 "app_name": "Postman",
 "type_name": "Native",
 "domain_site": "",
 "employee_id": "2223eb0f0d0c4941a16e83dc7274771b",
 "count_seconds": 9.65
 }
 ],
 "granula_end": "17:55:00",
 "granula_start": "17:50:00"
 },
 {
 "apps": null,
 "granula_end": "18:00:00",
 "granula_start": "17:55:00"
 }
]
```

I have the following task:
For a given date range, calculate the number of minutes in each application with an interval of 15 minutes. That is, I need to combine the existing 5-minute granules into 15-minute granules and calculate the amount of time for all applications in this granule for each granule. Group the list of applications itself so that there are no duplicates. I tried to do this, but I can’t figure out how to write the query correctly.

and I need a second query that finds the largest granule by application runtime. That is, where the greatest amount of work is in different applications. I don’t understand how to write this query at all...

Here is a link to my work - https://dbfiddle.uk/q88VfV3l

P.S. I looked through different questions on the forum, but none of them helped me solve the problem, so I posted my question.