How to obtain the most recent modified account’s contact information Using SAQL

Problem Statement:

The business wants to see each account’s most recent changed contact details.
Let’s take a look at the example data below for an illustration. In this business wants to see the contact that was recently updated.

Sample Data

Solution Approach:

The approach I will take for this use case is Use row_number() windowing function to rank the records based on the Contact Last modified Dates.

Window Function Syntax :

<windowfunction>(<projection expression>) over (<row range> partition by <reset groups> order by <order clause>) as <label>

for more information about the SAQL Windowfunction refer the below documentation.

Windowing Functions | Analytics SAQL Developer Guide | Salesforce Developers

The steps would be:

1. load the dataset, group with the two dimensions and one Dates (Account Id, Contact Id, Contact Last Modified Date)

q = load "Contact";
q = group q by ('AccountId', 'Id', 'LastModifiedDate_Year', 'LastModifiedDate_Month', 'LastModifiedDate_Day');

2. Project the grouped dimensions and date, then use the row_number() windowing function to generate a derived measure to determine which records have been updated most recently.

Formula :

row_number() over([..] partition by ('AccountId') order by ('LastModifiedDate_Year' desc, 'LastModifiedDate_Month' desc, 'LastModifiedDate_Day' desc)) as 'OrderOfModifiedContact';

Projection statement :

q = foreach q generate 'AccountId' as 'AccountId', 'Id' as 'Id', 'LastModifiedDate_Year' as 'LastModifiedDate_Year', 'LastModifiedDate_Month' as 'LastModifiedDate_Month', 'LastModifiedDate_Day' as 'LastModifiedDate_Day',row_number() over([..] partition by ('AccountId') order by ('LastModifiedDate_Year' desc, 'LastModifiedDate_Month' desc, 'LastModifiedDate_Day' desc)) as 'OrderOfModifiedContact';

3. Our current goal is to view the latest modified contact of Account subgroup.so Windowing function derived field should be filtered when it equals 1. Then project the fields that must appear in the final output.

q = filter q by 'OrderOfModifiedContact' == 1 ;

q = foreach q generate  'AccountId','Id','LastModifiedDate_Year'+"~~~"+'LastModifiedDate_Month'+"~~~"+'LastModifiedDate_Day' as 'LastModifiedDate_Year+"~~~"+LastModifiedDate_Month+"~~~"+LastModifiedDate_Day' ;

Note:

Since numerical filters have an influence on performance, you should avoid using them. Instead, use the Window function in the case statement to flag records where the order of the records is 1. The flag field may then be used to filter out records.

the following the query for this alternative

(case when (row_number() over([..] partition by ('AccountId') order by ('LastModifiedDate_Year' desc, 'LastModifiedDate_Month' desc, 'LastModifiedDate_Day' desc))) == 1 then "Yes" else "No" end )as 'OrderOfModifiedContact';

q = foreach q generate 'AccountId' as 'AccountId', 'Id' as 'Id', 'LastModifiedDate_Year' as 'LastModifiedDate_Year', 'LastModifiedDate_Month' as 'LastModifiedDate_Month', 'LastModifiedDate_Day' as 'LastModifiedDate_Day',(case when (row_number() over([..] partition by ('AccountId') order by ('LastModifiedDate_Year' desc, 'LastModifiedDate_Month' desc, 'LastModifiedDate_Day' desc))) == 1 then "Yes" else "No" end )as 'OrderOfModifiedContact';
q = filter q by 'OrderOfModifiedContact' == "Yes" ;

The final query should now look similar to this:

q = load "Contact";

q = group q by ('AccountId', 'Id', 'LastModifiedDate_Year', 'LastModifiedDate_Month', 'LastModifiedDate_Day');

q = foreach q generate 'AccountId' as 'AccountId', 'Id' as 'Id', 'LastModifiedDate_Year' as 'LastModifiedDate_Year', 'LastModifiedDate_Month' as 'LastModifiedDate_Month', 'LastModifiedDate_Day' as 'LastModifiedDate_Day',row_number() over([..] partition by ('AccountId') order by ('LastModifiedDate_Year' desc, 'LastModifiedDate_Month' desc, 'LastModifiedDate_Day' desc)) as 'OrderOfModifiedContact';

q = filter q by 'OrderOfModifiedContact' == 1 ;

q = foreach q generate  'AccountId','Id','LastModifiedDate_Year'+"~~~"+'LastModifiedDate_Month'+"~~~"+'LastModifiedDate_Day' as 'LastModifiedDate_Year+"~~~"+LastModifiedDate_Month+"~~~"+LastModifiedDate_Day' ;

Output :

Final Output