Review SQL Examples

Learning Objectives

After completing this unit, you’ll be able to:

• Recognize the functions of a SQL statement.
  
• Include a time frame in a streaming insights SQL statement.
  
• Find resources to help build SQL.
  

Streaming Insights in Data Cloud

The best way to learn to build insights with SQL is to review example statements. Once you can identify what each section of a SQL statement does, you can recognize patterns that can be used to build your own statements. Before we view examples, it’s important to know that there are two types of insights: Calculated and streaming. Calculated insights are used to query and create complex calculations based on stored data, while streaming insights are queries based on real-time data.

And with streaming insights, you can:

• Generate time series analytics on continuously moving data.
  
• Find useful patterns, and share the insights with other apps via Data Actions.
  
• Author using the insights builder and SQL.
  
• Use with Java Database Connectivity (JDBC) API and visualizations tools such as Tableau.
  

SQL Examples of Calculated Insights

Let’s start with a few calculated insights examples. This first one calculates email engagement data found within Marketing Cloud and groups it with unified individual profile data.

SELECT COUNT( EmailEngagement__dlm.Id__c) as email_open_count__c,
UnifiedIndividual__dlm.Id__c as customer_id__c
FROM
EmailEngagement__dlm
JOIN
IndividualIdentityLink__dlm
ON
IndividualIdentityLink__dlm.SourceRecordId__c = EmailEngagement__dlm.IndividualId__c


and IFNULL(IndividualIdentityLink__dlm.KQ_SourceRecordId__c, ‘’) = IFNULL(EmailEngagement__dlm.KQ_IndividualId__c, ‘’)
and EmailEngagement__dlm.EngagementChannelActionId__c ='Open'
JOIN
UnifiedIndividual__dlm
ON
UnifiedIndividual__dlm.Id__c = IndividualIdentityLink__dlm.UnifiedRecordId__c
GROUPBY
customer_id__c

Let’s break down each section of this SQL statement.

Section 1

SELECT COUNT( EmailEngagement__dlm.Id__c) as email_open_count__c,
UnifiedIndividual__dlm.Id__c as customer_id__c

What It Does: Calculate the count of emails opened according to unified individuals, also called the customer ID.

Section 2

FROM
EmailEngagement__dlm

What It Does: Locate this information in the email engagement DMO.

Section 3

JOIN
IndividualIdentityLink__dlm
ON
IndividualIdentityLink__dlm.SourceRecordId__c = EmailEngagement__dlm.IndividualId__c
and IFNULL(IndividualIdentityLink__dlm.KQ_SourceRecordId__c, ‘’) = IFNULL(EmailEngagement__dlm.KQ_IndividualId__c, ‘’)
and EmailEngagement__dlm.EngagementChannelActionId__c ='Open'
JOIN
UnifiedIndividual__dlm
ON
UnifiedIndividual__dlm. Id__c = IndividualIdentityLink__dlm.UnifiedRecordId__c

What It Does: In this step, connect the email engagement DMO with the individual identity Link DMO. Connect them using the foreign keys of source record ID and individual ID and respective key qualifier attributes, and join based on email engagement equaling an email open. Also connect this data with the unified individual DMO based on the ID and unified record ID.

Section 4

GROUPBY
customer_id__c

What It Does: Group this information based on customer ID.

Before we move on, there’s one more thing to note about the previous example. The way UnifiedIndividual object is related to an engagement object (such as EmailEngagement) is with a bridge object (such as Individual Identity link), which contains the mapping of Unified Individual ID and Individual ID.

Next let’s look at another SQL example using a ranking function. This statement calculates customer spend and then uses it to rank customers based on the spend across all of the Unified Individuals.

SELECT
UnifiedIndividual__dlm.ssot__Id__c AS customer_id__c,
RANK() OVER (ORDER BY SUM(ssot__SalesOrder__dlm.ssot__GrandTotalAmount__c) ) AS customer_rank_based_on_spend__c,


SUM(ssot__SalesOrder__dlm.ssot__GrandTotalAmount__c) AS customer_spend__c
FROM
ssot__SalesOrder__dlm
JOIN
IndividualIdentityLink__dlm
ON (ssot__SalesOrder__dlm.ssot__SoldToCustomerId__c = IndividualIdentityLink__dlm.SourceRecordId__c)
AND IFNULL(ssot__SalesOrder__dlm.KQ_SoldToCustomerId__c, ‘’) = IFNULL(IndividualIdentityLink__dlm.KQ_SourceRecordId__c, ‘’)
LEFT OUTER JOIN UnifiedIndividual__dlm
ON (IndividualIdentityLink__dlm.UnifiedRecordId__c = UnifiedIndividual__dlm.ssot__Id__c)
GROUP BY customer_id__c
HAVING RANK() OVER (ORDER BY SUM(ssot__SalesOrder__dlm.ssot__GrandTotalAmount__c) ) < 1000

Let’s break this statement down further.

Section 1

SELECT
UnifiedIndividual__dlm.ssot__Id__c AS customer_id__c,
RANK() OVER (ORDER BY SUM(ssot__SalesOrder__dlm.ssot__GrandTotalAmount__c) ) AS customer_rank_based_on_spend__c,
SUM(ssot__SalesOrder__dlm.ssot__GrandTotalAmount__c) AS customer_spend__c

What It Does: Across all unified individuals, rank each customer based on total spend.

Section 2

FROM
ssot__SalesOrder__dlm

What It Does: Look for this info in the Sales Order DMO.

Section 3

JOIN
IndividualIdentityLink__dlm
ON (ssot__SalesOrder__dlm.ssot__SoldToCustomerId__c = IndividualIdentityLink__dlm.SourceRecordId__c)
AND IFNULL(ssot__SalesOrder__dlm.KQ_SoldToCustomerId__c, ‘’) = IFNULL(IndividualIdentityLink__dlm.KQ_SourceRecordId__c, ‘’)
LEFT OUTER JOIN UnifiedIndividual__dlm
ON (IndividualIdentityLink__dlm.UnifiedRecordId__c = UnifiedIndividual__dlm.ssot__Id__c)

What It Does: Join data from the sales order DMO to the individual identity link DMO on customer ID and individual ID, and their respective key qualifier attributes. Join with some matching data in the unified individual DMO based on unified record ID and ID.

Section 4

GROUP BY customer_id__c

What It Does: Group this information based on customer ID.

Section 5

HAVING RANK() OVER (ORDER BY SUM(ssot__SalesOrder__dlm.ssot__GrandTotalAmount__c) ) < 1000

What It Does: Include less than 1000 customers according to their total spend amount.

Streaming Insights

Now that we’ve reviewed some examples for calculated insights, let’s switch to build SQL for streaming insights. Building a streaming insight with SQL is similar to building a calculated insight, except you need to consider a window of time.

Streaming Insights SQL Example

Let’s look at an example that shows page views within a time frame of 5 minutes.

SELECT COUNT( RealTimeMobileEvents__dlm.pageviews__c ) as page_views__c,
ssot__Individual__dlm.ssot__Id__c as customer_id__c,
ssot__Individual__dlm.KQ_Id__c as kq_customer_id__c,
RealTimeMobileEvents__dlm.product__c as product__c,
WINDOW.START as start__c,
WINDOW.END as end__c
FROM
RealTimeMobileEvents__dlm
JOIN
 ssot__Individual__dlm
ON
ssot__Individual__dlm.ssot__Id__c = RealTimeMobileEvents__dlm.deviceId__c
AND IFNULL(ssot__Individual__dlm.KQ_Id__c, ‘’) = IFNULL(RealTimeMobileEvents__dlm.KQ_deviceId__c, ‘’)
GROUP BY
window( RealTimeMobileEvents__dlm.dateTime__c ,'5 MINUTE'),
Customer_id__c, kq_customer_id__c

The noticeable difference in this SQL statement from the calculated insights are the WINDOW commands. These define how your results are grouped—in this example, within 5 minutes.

WINDOW.START as start__c,
WINDOW.END as end__c
GROUP BY
window( RealTimeMobileEvents__dlm.dateTime__c ,'5 MINUTE'),

And here is an example result of this expression.

Let’s see another example.

SELECT
SUM(
MobileApp_RT_Events__dlm.productPurchaseWeb_orderQuanity__c
) as order_placed__c,
MobileApp_RT_Events__dlm.AddToCartWeb_productId__c as product__c,
WINDOW.START as start__c,
WINDOW.ENDas end__c
FROM
MobileApp_RT_Events__dlm
GROUPBY
window(
MobileApp_RT_Events__dlm.dateTime__c,
'5 MINUTE'
),
MobileApp_RT_Events__dlm.AddToCartWeb_productId__c

Let’s break down each section of this statement.

Section 1

SELECT
SUM(
MobileApp_RT_Events__dlm.productPurchaseWeb_orderQuanity__c
) as order_placed__c,
MobileApp_RT_Events__dlm.AddToCartWeb_productId__c as product__c,
WINDOW.START as start__c,
WINDOW.END as end__c

What It Does: Find the sum of the orders placed based on the MobileApp events streaming source between a start and end time.

Section 2

FROM
MobileApp_RT_Events__dlm

What It Does: Use the MobileApp events streaming source.

Section 3

GROUPBY
window(
MobileApp_RT_Events__dlm.dateTime__c,
'5 MINUTE'
),
MobileApp_RT_Events__dlm.AddToCartWeb_productId__c

What It Does: Group the results into 5 minute aggregations according to product ID and include info about: order quantity placed, product, start and end time identified.

Build Your Statements

Now that you have the basic concepts and some examples, explore the various options you have when building your insights. There are many more functions that you can add to your SQL statements to further your results.

• Check out some specific SQL Rules in Calculated Insights.
  
• Explore our repository of SQL examples in a Data Cloud Git Hub Repo.
  

Finally, you can visit the Data Cloud Insight help page to learn how to create calculated insights. And with that, you’re ready to unlock the power of a SQL query within Data Cloud.

Resources

• Salesforce Help: Calculated Insights
• Salesforce Help: Using ANSI SQL Statements in Data Cloud
• Salesforce Help: General SQL Rules in Calculated Insights
• Salesforce Help: Using the JDBC Driver in Data Cloud
• External: Salesforce GitHub, Data Cloud Calculated Insights