SQL script for joining two tables and only showing results with the most recent timestamp?

Question

Suppose I am a bank:

I have a table with a set of user identities called user_identity, and another user_balance with a list of every balance a user has ever had.

A new entry is created in user_balance every time a user deposits or withdraws money from their account. Each entry has the following fields:

timestamp - the date of the recorded balance
id - unique identifier
userId - id of the user who owns the account
amountCents - dollar amount in cents of the balance
relatedTransferId - unique identifier of the withdrawal or deposit that led there to be a new balance created
previousBalanceId - unique identifier of the balance that came before it

I want to write two scripts:

One that returns the sum of all the money currently stored across all accounts
One that returns a list of all users and their current balance

Here is my attempt at writing the second one:

SELECT 
    user_balance."amountCents" * (1/100) AS balance, 
    "user"."identityId",
    user_identity."firstName",
    user_identity."lastName",
    user_balance.timestamp
FROM user_balance
JOIN user_identity
    ON user_identity.id = user_balance."userId"
ORDER BY 1;

I don't know how to select only the most recent balance for a given user. Any and all help would be very appreciated!

user_balance sample table (notice how there may be more than one entry per user id because there is an entry for each time the balance changes!):

            timestamp           |     userId      | amountCents
--------------------------------+-----------------+--------------
  2021-05-12 07:02:05.088+00:00 | u_2przr9gcn52sm |       20607
  2021-05-12 17:08:05.552+00:00 | u_ysvzqzs7f862  |           0
  2021-05-19 00:40:29.62+00:00  | u_y9eyf0j3b5i5  |      407454
  2021-05-16 21:47:34.123+00:00 | u_jze8wwmuuudk  |       66500
  2021-05-14 08:01:03.929+00:00 | u_22cyb1sctel5k |     3881602
  2021-05-17 05:01:02.711+00:00 | u_22cyb1sctel5k |     3435703

user_identity sample table:

         id        |         firstName          |      lastName
-------------------+----------------------------+----------------------
  ui_104xy9vczsrmy | John                       | Doe
  ui_10holeidg2ydt | John                       | Doe
  ui_10no0d6mtvdfi | John                       | Doe

Desired result:

      firstName    |         lastName           |      currentBalance
-------------------+----------------------------+----------------------
  John             | Doe                        | 3000
  John             | Doe                        | 2000
  John             | Doe                        | 1000

(where currentBalance in the final table is the balance associated with the most recent timestamp inside the user_balance table).

Please add a few rows of sample data and the expected result. — The Impaler, May 19 '21 at 22:15
Hey @TheImpaler! Added rows of sample data and expected result. Your help is much appreciated — veex, May 19 '21 at 23:30

score 0 · Answer 1 · answered May 20 '21 at 01:15

You can use ROW_NUMBER() on a table expression to identify the rows you want and exclude the other ones.

For example:

select
  i.firstName,
  i.lastName,
  x.amountCents as currentBalance
from user_identity i
left join (
  select b.*,
    row_number() over(partition by b.userId 
                      order by b.timestamp desc) as rn
  from user_balance b
) x on x.id = i.userId
where x.rn = 1

SQL script for joining two tables and only showing results with the most recent timestamp?

1 Answers1