How to calculate user balance?

Question

SQL

This is one of the tasks, the execution time is 11 minutes after opening the spoiler.

spoiler

Create a query to calculate balance for each player. Balance is calculated in a following way:
List must have following columns:

deposit + adjustment_up - adjustment_down - rollback (referenced deposit amount) - rollback (referenced adjus

name           description

balance => result of provided formula
owner_id => ID of wallet owner

tables:
user (id, email STRING)
wallet (id, user_id FK INT)
transaction (id, type STRING, amount INT, wallet_id FK INT)

Примечание, deposit ето тип транзакции, остальные переменные лично без понятия откуда и что там, такова задача, вопросы задавать было невозможно т.к. задача в атономном режиме, решил или нет, без вопросов. Соответственно завалил так же без вопросов. Могу предположить что то все динамические параметры и каково их значение не влияет на решение.

The question is to get at least an approximate solution, to understand how to form a subquery with a formula for counting field values ​​from other tables and external dynamic variables, parameters.

UPD. adjustment_down, deposit, adjustment_up are transaction types. I am posting the task after 3 weeks, so I already forgot the values, I think the company found who they wanted in this time.

Answer 1

SELECT usesr.id, SUM(CASE WHEN transaction.type IN ('deposit', 'adjustment_up',)
                          THEN amount
                          WHEN transaction.type IN ('adjustment_down', 'rollback')
                          THEN -amount
                          ELSE 0
                          END) balance
FROM user
JOIN wallet ON iser.id = wallet.user_id
JOIN transaction ON wallet.id = transaction.wallet_id
GROUP BY user.id

If it is possible that the user has no transactions, then use LEFT JOIN and wrap SUM() in COALESCE().
PS. Keeping in mind the previous topic. Complexity - the same 2/10, the net time to write the entire answer is 2 minutes 15 seconds (only the request is 1 minute 25 seconds).

Answer 2

I can assume that adjustment_up, adjustment_down and rollback are also types of transactions. You need to find all such transactions in the transaction table, related to a specific wallet_id and substitute it in the formula.
Next, you need to calculate the total balance for wallets owned by one user, and give it as a result.
PS Where is the task from?