I have a table (let's call it status_log) that has the following columns:
id entity_id status_id created_at
All the columns are not nullable and of type uuid apart from the last one which is as the name suggests is a timestamptz.
Every time a status of entity changes, a new row is added to this table with a new status_id and current created_at timestamp (note that the previous records are not deleted).
I need to write a query that would do the following:
Calculate the number of entries for a custom array of status_id's for each day over a custom period of time
Explanation:
Let's say we are given an array of status_id's [1, 2, 3] and two timestamps 10 and 20. Then I would need to calculate how many entities had each of given status'es over the given period of time
Example:
Data in the table (for simplicity reasons, created_at is given as an integer, you may treat it as a day)
id entity_id status_id created_at
1 1 1 4
2 2 2 6
3 3 2 7
4 2 1 8
5 1 2 10
Input statuses: [1, 2]
Input period of time: from 4 till 10
Expected output:
Array<dayNumber: {
statusId: count
}>
[
4: {
1: 1,
2: 0
},
5: {
1: 1,
2: 0
},
6: {
1: 1,
2: 1
},
7: {
1: 1,
2: 2
},
8: {
1: 2,
2: 1
},
9: {
1: 2,
2: 1
},
10: {
1: 1,
2: 2
},
]
Assumptions:
You may assume that the time period range will always include at least one day so there is always something to display.
Technical requirements:
The query has to be very efficient as the number of rows inside the table can potentially be 30k+
What I have tried so far:
Honestly, I don't really know where to start, it seems like an advanced query to me so after hours of research and trial & error I couldn't reach something that's worth putting here. Any help/guidance is highly appreciated!