Select with subquery to catch previous record

Question

I have a user readout log structure by reference. As shown below.

SELECT L.LeituraID, L.UsuarioID, L.Referencia, L.Leitura
FROM Leituras AS L

|-----------|-----------|------------|---------|
| LeituraID | UsuarioID | Referencia | Leitura |
|-----------|-----------|------------|---------|
| 1         | 1         | 01/01/2018 | 52      |
| 2         | 2         | 01/01/2018 | 157     |
| 3         | 3         | 01/01/2018 | 73      |
| 4         | 1         | 01/02/2018 | 63      |
| 5         | 2         | 01/02/2018 | 171     |
| 6         | 3         | 01/02/2018 | 89      |
|-----------|-----------|------------|---------|

I need to take the user reading data by a reference date and make a consumption calculation.

To calculate the consumption you need to have the previous reading information.

I assembled the query by bringing the current reading data and a subquery to pick up the previous reading.

The problem is that this search with the subquery is taking too long.

Is there any way to get that previous reading in a faster way?

WITH cteLeituras AS (
    SELECT 
        L.LeituraID
        , L.UsuarioID
        , L.Referencia
        , L.Leitura
        (
            SELECT TOP 1 LA.Leitura 
            From Leituras AS LA 
            WHERE LA.UsuarioID = L.UsuarioID AND LA.Referencia < L.Referencia
            ORDER BY LA.Referencia DESC
        ) AS LeituraAnterior
    FROM Leituras AS L
    WHERE L.Referencia = '2018-02-01'
), cteLeiturasConsumo AS (
    SELECT 
        L.LeituraID
        , L.UsuarioID
        , L.Referencia
        , L.Leitura
        , L.LeituraAnterior
        , (L.Leitura - L.LeituraAnterior) AS Consumo
    FROM cteLeituras AS L 
)

|-----------|-----------|------------|---------|-----------------|---------|
| LeituraID | UsuarioID | Referencia | Leitura | LeituraAnterior | Consumo |
|-----------|-----------|------------|---------|-----------------|---------|
| 4         | 1         | 01/02/2018 | 63      | 52              | 11      |
| 5         | 2         | 01/02/2018 | 171     | 157             | 14      |
| 6         | 3         | 01/02/2018 | 89      | 73              | 16      |
|-----------|-----------|------------|---------|-----------------|---------|

Answer 1

You can use the function ROW_NUMBER() signed by usuarioid to achieve the order of the readings and perform the calculation:

WITH ordenado AS (
  SELECT l.leituraid,
      l.usuarioid,
      l.referencia,
      l.leitura,
      ROW_NUMBER() OVER(PARTITION BY l.usuarioid ORDER BY l.referencia) AS ordem
  FROM leituras l
)
SELECT atual.*,
      (atual.leitura - ISNULL(anterior.leitura, 0)) AS consumo
  FROM ordenado atual
      LEFT JOIN ordernado anterior ON anterior.usuarioid = atual.usuarioid
                                  AND anterior.ordem = atual.ordem - 1
WHERE atual.referencia = '2018-02-01'

ROW_NUMBER()

Returns the sequential number of a row in a partition of a result set, starting at 1 for the first row of each partition.