I have a table with data on user applications. It contains several fields: employee_id text, date date, app_info jsonb.
+-------------+------------+------------+
| employee_id | date | app_info |
+-------------+------------+------------+
| 2223eb0f0d0x| 01/07/2025 | jsonb |
| 2223eb0f0d0x| 01/08/2025 | jsonb |
| 2223eb0f0d0x| 31/07/2025 | jsonb |
| 2223eb0f0d0x| 31/08/2025 | jsonb |
+-------------+------------+------------+
The info field app_info contains the following code:
[
{
"apps": [
{
"grade": "UNPRODUCTIVE",
"app_name": "Google chrome",
"type_name": "Native",
"domain_site": "http://vk.com",
"count_seconds": 57.731
}
],
"granula_end": "17:55:00",
"granula_start": "17:50:00"
},
{
"apps": [
{
"grade": "UNPRODUCTIVE",
"app_name": "Google chrome",
"type_name": "Native",
"domain_site": "http://vk.com",
"count_seconds": 217.879
},
{
"grade": "PRODUCTIVE",
"app_name": "Windows Explorer",
"type_name": "Native",
"domain_site": "",
"count_seconds": 3.174
}
],
"granula_end": "19:25:00",
"granula_start": "19:20:00"
},
{
"apps": [
{
"grade": "NEUTRAL",
"app_name": "Time Doctor 2",
"type_name": "Native",
"domain_site": "",
"count_seconds": 118.299
}
],
"granula_end": "19:30:00",
"granula_start": "19:25:00"
},
{
"apps": [
{
"grade": "NEUTRAL",
"app_name": "Time Doctor 2",
"type_name": "Native",
"domain_site": "",
"count_seconds": 29.992
},
{
"grade": "PRODUCTIVE",
"app_name": "Windows Explorer",
"type_name": "Native",
"domain_site": "",
"count_seconds": 3.002
}
],
"granula_end": "19:55:00",
"granula_start": "19:50: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.
P.S. I used your answers and am trying to create a new query based on the table provided in the question.
I need to calculate the total activity time for all applications in the granule for each 5-minute granule (these granules are already in the table).
Find the application that has the largest number of seconds, and I need to display the application name and productivity rating. Sort all applications in the granule by time and productivity rating.
Merge identical granules. Granules in which the most used application is the same are considered identical. After merging, get an extended granule with a count of the number of seconds. For example, we have granules:
[
{
"apps": [
{
"grade": "UNPRODUCTIVE",
"app_name": "Google chrome",
"type_name": "Native",
"domain_site": "http://vk.com",
"count_seconds": 57.731
}
],
"granula_end": "16:55:00",
"granula_start": "16:50:00"
},
{
"apps": [
{
"grade": "UNPRODUCTIVE",
"app_name": "Google chrome",
"type_name": "Native",
"domain_site": "http://vk.com",
"count_seconds": 217.879
},
{
"grade": "PRODUCTIVE",
"app_name": "Windows Explorer",
"type_name": "Native",
"domain_site": "",
"count_seconds": 3.174
}
],
"granula_end": "16:55:00",
"granula_start": "17:00:00"
}]
You can see that these two granules have the same most used application. And the granules are adjacent to each other in time! These are the granules that need to be merged into one:
{
"apps": [
{
"grade": "UNPRODUCTIVE",
"app_name": "Google chrome",
"type_name": "Native",
"domain_site": "http://vk.com",
"count_seconds": 275.61
},
{
"grade": "PRODUCTIVE",
"app_name": "Windows Explorer",
"type_name": "Native",
"domain_site": "",
"count_seconds": 3.174
}
],
"granula_end": "16:50:00",
"granula_start": "17:00:00"
}
Only adjacent granules need to be merged. And the granula_start and granula_end fields are changed into one granule per 10 minutes. This merging rule applies to all granules, both lower and higher in time. I understand that I will need to use recursion here, but I can't write such a query.
I also couldn't select not just the maximum number of seconds in the array of applications, but immediately an object that contains the maximum number of seconds with information about the application name and productivity rating.
There is also a rule for sorting by productivity ratings: unproductive, productive, no rating, neutral, inaction. And you need to sort all the applications in each granule according to this order. But first we sort by time, and then by rating. As a result, I should get a query like this:
{
"userInfo": {
"employeeId": "2223eb0f0d0c4941a16e83dc7274771b",
},
"granules": [
{
"dateTimeStart": "2025-07-08T12:30",
"dateTimeEnd": "2025-07-08T12:35",
"highestTypeActivity": "PRODUCTIVE",
"activeTime": 210,
"apps:" [
{
"name": "telegram.exe",
"activeTime": 140,
"grade": "PRODUCTIVE"
},
{
"name": "notepad.exe",
"activeTime": 70,
"grade": "PRODUCTIVE"
}
]
},
{
"dateTimeStart": "2025-07-08T12:35",
"dateTimeEnd": "2025-07-08T12:40",
"activeTime": 210,
"apps:" [
{
"name": "google chrome",
"details": "http://vk.com",
"activeTime": 140,
"grade": "UNPRODUCTIVE"
},
{
"name": "google chrome",
"details": "http://mail.ru",
"activeTime": 70,
"grade": "PRODUCTIVE"
}
]
},
]
}
I am update table in https://dbfiddle.uk/K5MnXSjx
