SOQL - Get Contact with More than N (7) Activity in Last-x-Month

Question

I am to prepare a query in SOQL which will return me only contacts that have more than 7 activities in last 12 months.

I am able to fetch the contacts but not sure how I can use "ActivityHistory" table in the query.

Goal is to Prepare Query for

"Get contacts for which we have more than 7 activities in last 12 Month"

To Elaborate more:

Select c.Name, c.Id, (Select Id, IsTask, ActivityDate, ActivityType, 
CallType From ActivityHistories) From Contact c where LastActivityDate = 
LAST_N_MONTHS:12

Here I can get Contacts with any activity in last 12 months.

I am working to find how we can filter only contacts with more than "x" activities.

Answer 1

Did you try https://developer.salesforce.com/docs/atlas.en-us.api.meta/api/sforce_api_objects_openactivity.htm OpenActivity Object?

Not sure what you have tried so far, but seems like this query would help: e.g given in the link I shared above:

SELECT 
   (SELECT ActivityDate, Description 
    FROM OpenActivities 
    ORDER BY ActivityDate ASC NULLS LAST, LastModifiedDate DESC 
    LIMIT 500)
FROM Account
WHERE Name = 'Acme'
LIMIT 1

I tried following -

    Select id, name, 
(Select id, subject from OpenActivities where who.type = 'Contact') 
from contact

Answer 2

I think query similar to this may work.

SELECT Id, (SELECT Id, Subject, CreatedDate FROM ActivityHistories WHERE CreatedDate = LAST_N_DAYS: 30) FROM Account

refer this blog

Using Apex:

List<Contact>  cntList = new List<Contact>();

 for(Contact c: [SELECT Id, (SELECT Id, Subject, CreatedDate FROM ActivityHistories WHERE CreatedDate = LAST_N_DAYS: 30) FROM Contact])
 {
  if(c.ActivityHistories.size()>7)

  {
    cntList.add(c);
  }

 }