PostgreSQL sort by max date and get the difference

Question

I've got a table with user id, balance (amount only increases) and date created. So, each month I can get last amount by user and date created.
I'm doing it like that:

SELECT balance
FROM balances
WHERE id IN (
 SELECT max(id)
 FROM balances
 WHERE "userId" = 123
 AND datecreated IN (
 SELECT max(datecreated)
 FROM balances 
 WHERE "userId" = 123
 AND EXTRACT(YEAR FROM date(datecreated)) = 2018
 AND EXTRACT(MONTH FROM date(datecreated))::integer = 8
 )
)

First question: can it be done somehow better?
Second question: I can find the income by finding difference between current month last balance and previous month last balance.

SELECT (
SELECT balance
FROM balances
WHERE id IN (
 SELECT max(id)
 FROM balances
 WHERE "userId" = 123
 AND datecreated IN (
 SELECT max(datecreated)
 FROM balances 
 WHERE "userId" = 123
 AND EXTRACT(YEAR FROM date(datecreated)) = 2018
 AND EXTRACT(MONTH FROM date(datecreated))::integer = 8
 )
) - (
SELECT balance
FROM balances
WHERE id IN (
 SELECT max(id)
 FROM balances
 WHERE "userId" = 123
 AND datecreated IN (
 SELECT max(datecreated)
 FROM balances 
 WHERE "userId" = 123
 AND EXTRACT(YEAR FROM date(datecreated)) = 2018
 AND EXTRACT(MONTH FROM date(datecreated))::integer = 7
 )
)
) AS "month_income";

It works when I've got data for previous balance. But How can I select 0 if there is nothing in previous month? Tried to do CASE like:

SELECT 
 CASE WHEN balance IS NULL THEN 0
 ELSE balance
 END AS balance
FROM balances
WHERE id IN ( ... )

It does not return anything (even NULL value) when, for example, I'm setting WHERE id IN NULL. Also tried COALESCE (balance, 0). But the problem is that nothing returned.

Answer 1

If there is exactly one maximum balance per month, the first query can be simplified to

select balance 
from balances 
where user_id = 123 
 and date_trunc('month', datecreated) = date '2018-08-01'
order by datecreated desc 
limit 1;

The difference between a specific month and the previous one, can be calculated with a window function:

select b.datecreated, 
 b.balance,
 coalesce(prev.balance, 0) as prev_balance
from balances b
 left join lateral (
 select balance 
 from balances b2
 where b2.user_id = b.user_id
 and b2.datecreated < date_trunc('month', b.datecreated)
 order by datecreated desc 
 limit 1
 ) as prev on true
where user_id = 123 
 and date_trunc('month', datecreated) = date '2018-08-01'
order by datecreated desc 
limit 1;

Online example: https://rextester.com/WGTPBO61473

• Naryck: I think I need to specify, that new balance can be once per month, but can be even every day. So that is why I'm selecting not some previous balance, but making 2 times selection of max datecreated for current and previous month. In this case your query is not working
• user1822: @Naryck: my query works for multiple balances per month. See the online example