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?

Improve this question
5
  • 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
    Commented Aug 20, 2019 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
    Commented Aug 20, 2019 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
    Commented Aug 20, 2019 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
    Commented Aug 20, 2019 at 20:40
  • 1
    This will be on about half a million Contacts, about 100k Accounts
    – Cory O. Bennett
    Commented Aug 20, 2019 at 20:49

1 Answer 1

Reset to default
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
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .