How to correctly do subtraction in SELECT PostgreSQL?

Question

PostgreSQL

There is such a crm_reffin table :

id	play date	totalsum	refid
one	2016-02-27 18:47:29.850391	ten	eighteen
2	2016-02-27 18:47:43.309427	20	eighteen
3	2016-02-27 21:36:19.088732	100	eighteen

There is also a crm_referals table that contains the same id for the refid column from the crm_reffin table .
In addition to everything, there is also a log table , in which there is a lot of information, but there is also information on the transfer of funds by users.
In order to display information about how much the referrer earned and how much he received payments, the following query is executed:

SELECT playdate, totals as credit, (SELECT sum(debate) * 0.1 FROM log WHERE uid IN (SELECT id FROM users WHERE refid = (SELECT id FROM crm_referals WHERE uid = :uid))) AS sumdebet FROM crm_reffin WHERE refid = (SELECT id FROM crm_referals WHERE uid = :uid);

As a result, I get the following table:

pay date	credit	sumdebet
2016-02-27 18:47:29.850391	ten	8451
2016-02-27 18:47:43.309427	20	8451
2016-02-27 21:36:19.088732	100	8451

Where 10 and 20 are payout amounts and 8451 is the total accumulated payout amount.
I can’t figure out how to properly subtract from the remainder in one query so that the following table is obtained:

pay date	credit	sumdebet
2016-02-27 18:47:29.850391	ten	8451
2016-02-27 18:47:43.309427	20	8441
2016-02-27 21:36:19.088732	100	8421

Answer 1

You need to look towards the analytical functions lag and lead.