There is a table in ClickHouse that is constantly updated, format:
date_time | shop_id | item_id | status | balance
---------------------------------------------------------------
2022-09-09 13:00:01 | abc | 1234 | 0 | 0
2022-09-09 13:00:00 | abc | 1234 | 1 | 3
2022-09-09 12:50:00 | abc | 1234 | 1 | 10
The table stores statuses and balances for each item_id, when the balance is changed, a new record with status, time and balance is added. If the balance = 0, the status changes to 0.
Need to calculate how much time (how many minutes) every item_id in the shop was available for the day. The status may change several times a day.
Please help me calculate this.
