List Opportunities that have a previous StageName [Advanced SOQL]

Question

I'm trying to get a list of Opportunities that have now "Cancelled" (isClosed=true and isWon=false) stageName and the previous (Penultimate) value was "Active" by SOQL .

For example, the Opp X had these history

1. Created Active
2. Updated Active
3. Updated Paused
4. Updated Active
5. Updated Cancelled

This record should be return by the soql, but it shoudn't be returned if the step 4 doesn't exists.

In addition those opps should be and specific RecordType XX.

So far I've tried this:

Opportunity[] o =[select id from opportunity where isclosed = true and isWon = false  recordtype.name = 'XX'];
OpportunityHistory[] oh = [SELECT OpportunityId FROM OpportunityHistory WHERE StageName = 'Active' and OpportunityId in:o ];

But I cant figure out how to know that the second query is getting only the last previous Stage

Challenges

1. I have more than 10k Opps that should match

Answer 1

The OpportunityHistory does hold a record for each change of an opportunity. So if it went to stage Active, there will be a record for that. So it should be possible to execute the following query:

SELECT Id
FROM Opportunity
WHERE IsClosed = true
AND IsWon = false
AND RecordType.name = 'XX'
AND Id IN (
    SELECT OpportunityId
    FROM OpportunityHistory
    WHERE StageName = 'Active'
)

It selects all Opportunities, which are not won and closed from a certain record type, if there is a history entry indicating that the Opoortunity has been Active once.

It is still debatable, if it will perform adequately with thousands of Opportunities and millions of OpportunityHistory entries. I would give it a try, since the query can be performed from the Developer Console without much effort.