SOQL Query to count only Accounts with Opps in both this year and last year?

Question

So rather than create an Apex class, run multiple queries then write some code to figure out the answer, I thought I'd take a stab at writing a complex query that would get me the answer I'm looking for. Naturally, it's harder than I anticipated...

The goal is to calculate our 'renewal rate'. Here's the formula:

# of Accounts with both a current* closed/won opp and a previous* closed/won opp
(divided by)
# of Accounts with a current closed/won opp, regardless of previous opps

*'current' and 'previous' in this scenario are determined by two fields on the opp, 'effective date' and 'expiration date'. If today's date falls in between those two values, the opp is 'current'. An opp is a 'previous' opp if it is expired, but the expiration date falls within the past 365 days.

---

Question 1

How can I get a parent-to-child relationship query to only return parent records if the child subquery returns results? For example:

SELECT ID, 
      (SELECT StageName, Effective_Date__c, Expiration_Date__c 
       FROM Opportunities
       WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
FROM Account

Will return ALL my accounts, regardless of whether any matching opps are returned.

---

Question 2

Apparently I can't do an aggregate query (eg "SELECT COUNT(ID)") that also has a child subquery. If that's the case, what's the best way to get a count of the Accounts that have the appropriate opportunities?

---

Question 3

I'm having difficulty getting the list of accounts that have BOTH a current opp and a previous opp. If I modify my opp subquery's WHERE clause (from question 1) to say

 WHERE StageName='Closed Won' AND 
(Effective_Date__c < TODAY AND Expiration_Date__c > TODAY) OR 
(Expiration_Date__c < TODAY AND Expiration_Date__c > LAST_365_DAYS

It wont just return accounts with BOTH current previous opps - it will return accounts with Current opps, OR current and previous opps, OR just previous opps.

---

... Yeesh... it might be better to just write the code after all.. or do a cross-object SF report and just count the totals in Excel...

Answer 1

Question 1:

You can just adjust your Where to clause to limit the result to only accounts that have children that match your criteria like this.

Accounts This Year

SELECT ID, 
      (SELECT StageName, Effective_Date__c, Expiration_Date__c 
       FROM Opportunities
       WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
FROM Account where Id In (Select AccountId From Opportunity WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)

Accounts Last year

SELECT ID, 
      (SELECT StageName, Effective_Date__c, Expiration_Date__c 
       FROM Opportunities
       WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
FROM Account where Id In (Select AccountId From Opportunity Where StageName = 'Closed AND Expiration_Date__c < TODAY AND Expiration_Date__c > LAST_365_DAYS)

Question 2

You can get the count like this

Accounts This Year

SELECT count(Id) mycount
    FROM Account where Id In (Select AccountId From Opportunity WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)

Accounts Last Year

SELECT count(Id) mycount
        FROM Account where Id In (Select AccountId From Opportunity Where StageName = 'Closed AND Expiration_Date__c < TODAY AND Expiration_Date__c > LAST_365_DAYS)

Question 3

You can use the same method, just combine the Where clauses from question 1

SELECT ID, 
      (SELECT StageName, Effective_Date__c, Expiration_Date__c 
       FROM Opportunities
       WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
FROM Account where Id In (Select AccountId From Opportunity WHERE StageName='Closed Won' AND Effective_Date__c < TODAY AND Expiration_Date__c > TODAY)
And Id In (Select AccountId From Opportunity Where StageName = 'Closed AND Expiration_Date__c < TODAY AND Expiration_Date__c > LAST_365_DAYS)