Untitled

SELECT engagementsDistinct.ssot__owneruserid__c  ssot__OwnerUserId__c,
       To_json(Collect_list(Named_struct('ENGAGEMENT_TYPE',
                                    engagementsDistinct.engagement_type,
                            'TIMESTAMP'
                            , engagementsDistinct.timestamp, 'CATALOG_ID',
                                    engagementsDistinct.catalog_id,
                                                 'MAGNITUDE',
                            engagementsDistinct.magnitude,
                                    'USER_ID',
                                                 engagementsDistinct.user_id,
                                    'PERSONALIZATION_ID',
       engagementsDistinct.personalization_id, 'RANK'
       ,
       engagementsDistinct.rank,
       'PERSONALIZATION_POINT_ID',
       engagementsDistinct.personalization_point_id,
       'PERSONALIZER_ID',
       engagementsDistinct.personalizer_id,
       'ENGAGEMENT_CHANNEL',
       engagementsDistinct.engagement_channel))) activities
FROM   (SELECT joinedEngagements.ssot__owneruserid__c     ssot__OwnerUserId__c,
               joinedEngagements.engagement_type          ENGAGEMENT_TYPE,
               joinedEngagements.timestamp                TIMESTAMP,
               joinedEngagements.catalog_id               CATALOG_ID,
               joinedEngagements.magnitude                MAGNITUDE,
               joinedEngagements.user_id                  USER_ID,
               joinedEngagements.personalization_id       PERSONALIZATION_ID,
               joinedEngagements.rank                     RANK,
               joinedEngagements.personalization_point_id
               PERSONALIZATION_POINT_ID,
               joinedEngagements.personalizer_id          PERSONALIZER_ID,
               joinedEngagements.engagement_channel       ENGAGEMENT_CHANNEL
        FROM   (SELECT unifiedssotindividualtest__dlm.ssot__id__c
                       ssot__OwnerUserId__c,
                       engagements.engagement_type
                       ENGAGEMENT_TYPE,
                       engagements.timestamp                      TIMESTAMP,
                       engagements.catalog_id                     CATALOG_ID,
                       engagements.magnitude                      MAGNITUDE,
                       engagements.user_id                        USER_ID,
                       engagements.personalization_id
                       PERSONALIZATION_ID,
                       engagements.rank                           RANK,
                       engagements.personalization_point_id
                       PERSONALIZATION_POINT_ID,
                       engagements.personalizer_id
                       PERSONALIZER_ID,
                       engagements.engagement_channel
                       ENGAGEMENT_CHANNEL
                FROM   unifiedssotindividualtest__dlm
                       JOIN unifiedlinkssotindividualtest__dlm
                         ON unifiedssotindividualtest__dlm.ssot__id__c =
unifiedlinkssotindividualtest__dlm.unifiedrecordid__c
JOIN ssot__individual__dlm
ON unifiedlinkssotindividualtest__dlm.sourcerecordid__c =
ssot__individual__dlm.ssot__id__c
JOIN (SELECT temp.engagement_type,
        temp.user_id,
        temp.timestamp,
        temp.magnitude,
        temp.personalization_id,
        temp.catalog_id,
        temp.rank,
        temp.personalization_point_id,
        temp.personalizer_id,
        temp.engagement_channel
 FROM   (SELECT 'ssot__ProductOrderEngagement__dlm'
                        ENGAGEMENT_TYPE,
String(
ssot__productorderengagement__dlm.ssot__individualid__c)
USER_ID,
Date_format(
ssot__salesorderproductengagement__dlm.ssot__createddate__c,
'yyyy-MM-dd HH:mm:ss.SSS')
TIMESTAMP,
NULL
MAGNITUDE,
NULL
PERSONALIZATION_ID,
String(ssot__salesorderproductengagement__dlm.ssot__productid__c)
CATALOG_ID,
NULL
RANK,
NULL
PERSONALIZATION_POINT_ID,
NULL
PERSONALIZER_ID,
NULL
ENGAGEMENT_CHANNEL
FROM   ssot__productorderengagement__dlm) temp) engagements
ON engagements.user_id = ssot__individual__dlm.ssot__id__c)
joinedEngagements
GROUP  BY joinedEngagements.ssot__owneruserid__c,
joinedEngagements.engagement_type,
joinedEngagements.timestamp,
joinedEngagements.catalog_id,
joinedEngagements.magnitude,
joinedEngagements.user_id,
joinedEngagements.personalization_id,
joinedEngagements.rank,
joinedEngagements.personalization_point_id,
joinedEngagements.personalizer_id,
joinedEngagements.engagement_channel
ORDER  BY timestamp) engagementsDistinct
GROUP  BY engagementsDistinct.ssot__owneruserid__c