The example here uses 2 different approaches one using JOIN and another using **LAG** analytic function. In the table below I want to find the months that A store profiting more than B store. What I need is to first calculate the sum of monthly sales amount of each store and then subtract them. Note this table is only for demonstration hence it’s not normalized.

**Sales:**

ID | Store | SaleMonth | Amount |

1 | A | Jan | 120.00 |

2 | A | Feb | 80.50 |

3 | A | Feb | 200.00 |

4 | B | Jan | 160.50 |

5 | B | Feb | 70.00 |

6 | A | Feb | 130.00 |

7 | B | Mar | 180.00 |

8 | A | Mar | 110.50 |

How I did it was primitive and the performance was terrible with numerous records. I calculated the sums of both stores and then joined them together so I can calculate the result by subtracting 2 columns:

SELECT t.SaleMonth, netamount FROM (SELECT A.SaleMonth, A.Profit - B.Profit netamount FROM (SELECT Sales.SaleMonth, SUM(Amount) AS Profit FROM Sales WHERE Sales.Store = 'A' GROUP BY Sales.SaleMonth) A LEFT JOIN (SELECT Sales.SaleMonth, SUM(Amount) AS Profit FROM Sales WHERE Sales.Store = 'B' GROUP BY Sales.SaleMonth) B ON A.SaleMonth= B.SaleMonth) t WHERE t.netamount > 0;

A better approach is using the Oracle **LAG** analytic function. First sum the Amount grouping by SaleMonth & Store and then subtract between rows using **LAG**. This is roughly 5 times faster than the previous one in other test case:

SELECT s.SaleMonth, netamount FROM (SELECT t.SaleMonth, t.Amount- LAG(Amount, 1) OVER(PARTITION BY Sales.SaleMonth ORDER BY Sales.SaleMonth, Sales.Store) netamount FROM (SELECT Sales.SaleMonth, Sales.Store, SUM(Amount) AS Amount FROM Sales GROUP BY Sales.SaleMonth, Sales.Store) t) s WHERE s.netamount > 0;

The query result:

SaleMonth | netamount |

Feb | 340.50 |

Reference:LAG analytic function in Oracle SQL