PostgreSQL sort by max date and get the difference

postgresql

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.

Best Answer

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

Related Question