I'm looking for some idea how to calculate user debt in efficient way.

I have got 4 tables.

user_tab -- definitions of users
item_tab -- definitions of items that we can pay for (internet, phone, electricity, etc.)
payment_tab -- past and future payments that user have to pay before deadline
income_tab -- registered user incomes for specific item


I'm looking for efficient way for calculating user debt in specific date group by item_id.

For example for user_id = 1 and date = '2023-01-15' (this date is some variable) specific view should take all data from income_tab and sum all incomes for same item_id or null item_id together so we should have:

item_id | sum
1         720.00
2         390.00
3         145.00

in similar way should sum data from payment_tab but should consider only payment_tab items where deadline_date is <= '2023-01-15'

item_id | sum
1         460.00
2         290.00
3         357.00

As you can see there is a debt for item_id = 3. User should pay 357.00 however paid 145.00

I'm looking for some efficient query (view?) that will return information in that way:

user_id | item_id |  debt  | debt_days
1           3       212.00      90

dept is calculated as sum of payments - incomes debt_days should present amount of days from first payment that wasn't covered by incomes.

It's quite complicated for me ;) and I'm not sure if this is possible to calculate in some one advance query.

Thanks for advice


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.