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