Return to Revisions

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:

[
 {
 "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. 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.