0

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.

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

  • 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 – Naryck Nov 25 '18 at 11:49
  • @Naryck: my query works for multiple balances per month. See the online example – a_horse_with_no_name Nov 25 '18 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.