Ledger query using window functions

Question

This is a ledger kind of table, initial amount is when the movement is null and all transactions on it is based on the Waste_Seq

the query take the last entry (id) based on the movement and change the initial amount

this query seem to work for my scenario and seem fast, is there a better or alternative way of doing what the query does?

long term this table will have more than 5-10 millions rows easily, this is a new empty table for now

---

Schema and data:

CREATE TABLE [dbo].[fredou](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Movement] [int] NULL,
    [Waste_Seq] [int] NOT NULL,
    [Amount] [int] NOT NULL,
    [operation] [int] NOT NULL,
 CONSTRAINT [PK_fun_fred_test] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [NonClusteredIndex] ON [dbo].[fredou]
(
    [Waste_Seq] ASC,
    [Movement] ASC,
    [Id] DESC
)
INCLUDE (   [Amount],
    [operation]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

insert into fredou values (         null,         1,      1000,         1)
insert into fredou values (         1,         1,       500,        -1)
insert into fredou values (         1,         1,       450,        -1)
insert into fredou values (         1,         1,       475,        -1)
insert into fredou values (         1,         1,       475,        -1)
insert into fredou values (         2,         1,       300,         1)
insert into fredou values (         2,         1,       200,         1)
insert into fredou values (         2,         1,       100,         1)
insert into fredou values (         3,         1,       300,         0)
insert into fredou values (         3,         1,       350,         0)
insert into fredou values (         4,         1,        50,        -1)
insert into fredou values (         4,         1,        50,         0)
insert into fredou values (         null,         2,      5000,         1)
insert into fredou values (        50,         2,      1000,        -1)
insert into fredou values (        50,         2,      1000,         0)
insert into fredou values (        51,         2,       500,        -1)
insert into fredou values (        53,         2,       400,        -1)
insert into fredou values (        51,         2,       300,        -1);

Query:

DECLARE @SEQ int = 1

SELECT SUM(f.Amount * f.Operation) as Amount
FROM 
(
    SELECT  FIRST_VALUE(a.Amount)       OVER (partition BY a.Movement order by a.id desc) as Amount,
            FIRST_VALUE(a.Operation)    OVER (partition BY a.Movement order by a.id desc) as Operation,
            ROW_NUMBER()                OVER (partition BY a.Movement order by a.id desc) as rownum
    FROM [dbo].[fredou] a
    WHERE a.Waste_Seq = @SEQ 
) f
where f.rownum = 1;

Link to sql fiddle

Answer 1

I doubt FIRST_VALUE is necessary since you have where f.rownum = 1;