SQLite Forum

Hi,

Thanks for all the help you friends have provided in my silliness to get the right answers from my data. I appreciate the time each and one of you take to help out.

**the table:**
```
CREATE TABLE "calculategrowth" (
"transactionId" int,
"pig" int,
"growthpool" int,
"GrowthCarry" int
);
```

**sample data:**
```
INSERT INTO calculategrowth(TransactionId,pig,GrowthPool,GrowthCarry) VALUES(464,1000065,269250000,250000);
INSERT INTO calculategrowth(TransactionId,pig,GrowthPool,GrowthCarry) VALUES(645,1000065,134500000,1500000);
INSERT INTO calculategrowth(TransactionId,pig,GrowthPool,GrowthCarry) VALUES(3880,1000065,2430000000,0);
INSERT INTO calculategrowth(TransactionId,pig,GrowthPool,GrowthCarry) VALUES(9668,1000065,4338750000,750000);
INSERT INTO calculategrowth(TransactionId,pig,GrowthPool,GrowthCarry) VALUES(9949,1000065,210750000,1750000);
```

**Almost working query:**

```
SELECT *,
	(GrowthPool-GCOVAW)/1000000-EstGrowth as Diff
FROM (
  SELECT
	TransactionId,
	GrowthPool,

	lag(TransactionId,1) OVER
	w previous_transId, TransactionId as current_transId,
	((TransactionId) -  (lag(TransactionId,1,1) OVER w))*0.003*250 EstGrowth,

	lag(GrowthCarry) OVER
	w Previous_GCOV, GrowthCarry as Current_GCOV,
	(lag(GrowthCarry) OVER w) as GCOVAW

  FROM CalculateGrowth
  WINDOW w AS (PARTITION BY PIG ORDER BY PIG)
) e
WHERE EstGrowth IS NOT NULL;
```


**Results:**
```
┌───────────────┬────────────┬──────────────────┬─────────────────┬───────────┬───────────────┬──────────────┬─────────┬───────┐
│ TransactionId │ GrowthPool │ previous_transId │ current_transId │ EstGrowth │ Previous_GCOV │ Current_GCOV │ GCOVAW  │ Diff  │
├───────────────┼────────────┼──────────────────┼─────────────────┼───────────┼───────────────┼──────────────┼─────────┼───────┤
│ 464           │ 269250000  │                  │ 464             │ 347.25    │               │ 250000       │         │       │
│ 645           │ 134500000  │ 464              │ 645             │ 135.75    │ 250000        │ 1500000      │ 250000  │ -1.75 │
│ 3880          │ 2430000000 │ 645              │ 3880            │ 2426.25   │ 1500000       │ 0            │ 1500000 │ 1.75  │
│ 9668          │ 4338750000 │ 3880             │ 9668            │ 4341.0    │ 0             │ 750000       │ 0       │ -3.0  │
│ 9949          │ 210750000  │ 9668             │ 9949            │ 210.75    │ 750000        │ 1750000      │ 750000  │ -0.75 │
└───────────────┴────────────┴──────────────────┴─────────────────┴───────────┴───────────────┴──────────────┴─────────┴───────┘
```

**Problem:**

Since the **very first** record has no previous record to compare against, it needs a different formula for it.

EstGrowth: `TransactionId*0.003*250` for 348.00. You will notice mine is close, 
347.25

Also, since the diff is incorrect on the first one, the rest of the records are also wrong. The first needs this: `GrowthPool-EstGrowth`, which is -78.75

**Expected results:**
```
TransactionId,pig,GrowthPool,GrowthCarry,EstGrowth,Diff
464,1000065,269250000,250000,348,-78.75
645,1000065,134500000,1500000,135.75,-1.5
3880,1000065,2430000000,0,2426.25,2.25
9668,1000065,4338750000,750000,4341,-2.25
9949,1000065,210750000,1750000,210.75,-0.75
```

What needs to change to target the very first record? Do I need a `where` in a nested select or something?

Thanks for the teaching!