1

(Mode = the value that occurs most frequently in a given set of data.)

I have a Lookup field on Contacts which lists the Contact's Specialty. I'd like to find the mode of the Specialties of all Contacts for an Account and stamp that value on the Account. (This will run in batch job daily btw)

Here's what I have so far:

Initial Query: SELECT Id,AccountId, SpecialtyLookup__r.Name FROM Contact WHERE SpecialtyLookup__c != null

  1. Put the Account IDs in a Map
  2. For each Account in the Map find the mode of the related specialties

2.1 If there is a tie sort the list of the ties in alphabetical order and take the first option

  1. Stamp the winning Specialty in the Specialty__c field on the Account

Thoughts?

  • 2
    What do you mean by the "mode" of a list? Are you using "mode" here in the statistical sense (i.e. from the familiar trio of mean, median, and mode)? – Derek F Aug 20 at 19:17
  • Yes, I'm referring to the statistical sense Mode meaning: the value that occurs most frequently in a given set of data. – Cory O. Bennett Aug 20 at 19:21
  • I haven't worked with them enough to write a fully formed answer, but seems like GROUP BY ROLLUP or GROUP BY CUBE could put a lot of the work on the query. Not sure how aggregate queries work in the batch context, though, or how those functions work with fields queried across a relationship. – Thomas Taylor Aug 20 at 20:01
  • Right, I forgot, can't use AggregateQueries in Batch, but there are workarounds at the link. What kind of data volume would your query have? Note you'll need to solve for a single Account's contacts being spread across batches with a simple query. – Thomas Taylor Aug 20 at 20:40
  • 1
    This will be on about half a million Contacts, about 100k Accounts – Cory O. Bennett Aug 20 at 20:49
2

Outline of a solution:

start()

  • Query all Accounts

execute()

  • Do an aggregate query on Contact WHERE Contact.AccountId IN:scope. Group By accountId, Specialty and Count(Id)
  • Examine aggregate results and compute Mode (per Account)
  • Update Accounts

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.