SQL Use Cases for Insights Data 360

SELECT
COUNT(EmailEngagement__dlm.Id__c) as email_engagement_count__c,
UnifiedIndividual__dlm.Id__c as customer_id__c
FROM
 EmailEngagement__dlm
JOIN
 IndividualIdentityLink__dlm
ON
 EmailEngagement__dlm.IndividualId__c = IndividualIdentityLink__dlm.SourceRecordId__c
JOIN
 UnifiedIndividual__dlm
ON
 UnifiedIndividual__dlm.Id__c = IndividualIdentityLink__dlm.UnifiedRecordId__c
GROUP BY
customer_id__c

SELECT sub2.cust_id__c as id__c,
FIRST(sub2.rfm_recency__c*100 + sub2.rfm_frequency__c*10 +sub2.rfm_monetary__c) as rfm_combined__c,
FIRST(sub2.rfm_recency__c) as Recency__c,
FIRST(sub2.rfm_frequency__c) as Frequency__c,
FIRST(sub2.rfm_monetary__c) as Monetary__c
From (
 select UnifiedIndividual__dlm.Id__c as cust_id__c,
 ntile(4) over (order by MAX(SALESORDER__dlm.checkout_date__c)) as rfm_recency__c,
 ntile(4) over (order by SUM(SALESORDER__dlm.orderid__c)) as rfm_frequency__c,
 ntile(4) over (order by avg(SALESORDER__dlm.grand_total_amount__c)) as rfm_monetary__c
 FROM SALESORDER__dlm
LEFT JOIN
IndividualIdentityLink__dlm
on
SALESORDER__dlm.partyid__c=IndividualIdentityLink__dlm.SourceRecordId__c
LEFT Join
UnifiedIndividual__dlm
on
UnifiedIndividual__dlm.Id__c=IndividualIdentityLink__dlm.UnifiedRecordId__c
 group by cust_id__c
) as sub2
group by sub2.cust_id__c

SELECT COUNT( EmailEngagement__dlm.Id__c ) as email_count__c,
UnifiedIndividual__dlm.Id__c as customer_id__c
FROM
EmailEngagement__dlm
LEFT JOIN
 IndividualIdentityLink__dlm
ON
 IndividualIdentityLink__dlm.SourceRecordId__c = EmailEngagement__dlm.IndividualId__c
 and EmailEngagement__dlm.EngagementChannelActionId__c = 'Open'
JOIN
 UnifiedIndividual__dlm
ON
 UnifiedIndividual__dlm.Id__c = IndividualIdentityLink__dlm.UnifiedRecordId__c
GROUP BY
customer_id__c

SELECT
FIRST(SubQuery2.highest_purcahased_rank__c) as purchase_rank__c, 
SubQuery2.CustomerId__c as UnifiedCustomerID__c, 
SubQuery2.product_category__c as ProdSubCategory__c
FROM
    (
      SELECT 
            RANK() OVER ( PARTITION BY SubQuery1.id__c 
            order by (SubQuery1.product_purchase_count__c) desc ) 
            as highest_purcahased_rank__c, 
            SubQuery1.id__c as CustomerId__c, 
            SubQuery1.product_category__c as product_category__c
            FROM
                (
                SELECT 
                     UnifiedIndividual__dlm.Id__c as id__c,
                     PRODUCT__dlm.product_subcategory__c as product_category__c,
                     COUNT(PRODUCT__dlm.product_sk_u__c ) as product_purchase_count__c
                            FROM
                            PRODUCT__dlm
                            JOIN
                            SALESORDERPRODUCT__dlm
                            ON PRODUCT__dlm.productid__c=SALESORDERPRODUCT__dlm.productid__c
                            JOIN
                            SALESORDER__dlm 
                            ON SALESORDER__dlm.orderid__c=SALESORDERPRODUCT__dlm.orderid__c
                            LEFT JOIN
                            IndividualIdentityLink__dlm
                            on
                            SALESORDER__dlm.partyid__c=IndividualIdentityLink__dlm.SourceRecordId__c
                            LEFT Join
                            UnifiedIndividual__dlm
                            on
                           UnifiedIndividual__dlm.Id__c=IndividualIdentityLink__dlm.UnifiedRecordId__c
                             group by 
                             id__c, product_category__c
                ) as SubQuery1
    ) as SubQuery2
group by 
UnifiedCustomerID__c, ProdSubCategory__c