How to select the last value by date and the previous one in the same row?

Question

SQL Server

There is a table:

id date balance

Its approximate content:

id  date balance
1  2018-10-01  111
1  2018-10-02 115
12 2017-10-01 1134
... и тд

There are several million records in the table, where the id (non-unique) of the client, the date and its balance are entered daily.

It is necessary to make a query that will show the most recent client id, the date of its last balance change, the last balance and the date of the penultimate balance change and the penultimate balance, for each client in the database

id  date_current balance current_balance  date_previous_balance previous balance

This query returns the latest change:

SELECT
    id,
    date,
    balance
 FROM (
   SELECT
        cb.*,
        row_number() OVER (PARTITION BY id ORDER BY  date DESC) r
      FROM client_balance cb
    ) t
  WHERE r = 1
;

How to add the previous one to it?

Answer 1

You apparently never read and looked at how ROW_NUMBER works from the answer to the previous question !?
see the query output with ROW_NUMBER() !

SELECT
        b.*,
        row_number() OVER (PARTITION BY id ORDER BY balance_date DESC) r
      FROM balance b

in this case, row_number() numbers by id in descending order of the date and, accordingly, the previous record is number 2

Therefore, you just need to choose with r = 1 or r =2 , respectively!

for clarity, made a subquery with row_number in WITH - generalized_table_expression

WITH bal AS (
    SELECT
        b.*,
        row_number() OVER (PARTITION BY id ORDER BY balance_date DESC) r
      FROM balance b
)
SELECT id, balance_date, balance FROM bal WHERE r = 1 or r = 2
  ORDER BY id, balance_date desc
;

see db<>fiddle
UPDATE:
If we display the last and previous value in one line, then we select each as a subquery and combine the subqueries through LEFT JOIN (we join the previous one to the last one) by id. Through LEFT JOIN, so that all rows are selected from the left table (last values) and those rows that exist from the right (previous values) are joined, and if not, then NULL,
see below:

WITH
bal AS (
    SELECT
        b.*,
        row_number() OVER (PARTITION BY id ORDER BY balance_date DESC) r
      FROM balance b
),
last_bal AS (
  SELECT id, balance_date, balance FROM bal WHERE r = 1
),
prev_bal AS (
  SELECT id, balance_date, balance FROM bal WHERE r = 2
)
SELECT
    lb.id,
    lb.balance_date AS last_balance_date, lb.balance AS last_balance,
    pb.balance_date AS prev_balance_date, pb.balance AS prev_balance
  FROM last_bal lb
  LEFT JOIN prev_bal pb ON pb.id = lb.id
;

see db<>fiddle #2