Untitled
unknown
plain_text
2 months ago
4.6 kB
5
Indexable
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
Editor is loading...
Leave a Comment