Review SQL Examples

Learning Objectives

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

Recognize the functions of a SQL statement.
Include a timeframe 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 Engagement 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
GROUP BY
customer_id__c

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

Statement	What It Does
`SELECT COUNT( EmailEngagement__dlm.Id__c) as email_open_count__c,` `UnifiedIndividual__dlm.Id__c as customer_id__c`	Calculate the count of emails opened according to unified individuals, also called the customer ID.
`FROM` `EmailEngagement__dlm`	Locate this information in the Email Engagement DMO.
`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`	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.
`GROUP BY` `customer_id__c`	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.

Statement	What It Does
`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`	Across all unified individuals, rank each customer based on total spend.
`FROM` `ssot__SalesOrder__dlm`	Look for this info in the Sales Order DMO.
`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)`	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.
`GROUP BY customer_id__c`	Group this information based on customer ID.
`HAVING RANK() OVER (ORDER BY SUM(ssot__SalesOrder__dlm.ssot__GrandTotalAmount__c) ) < 1000`	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.

START_C	END_C	CUSTOMER_ID_C	PRODUCT_C	PAGE_VIEWS_C
12	12.05	1	HK0012	1
12.05	12.1	2	JK0078	2
12.1	12.15	3	HK0078	1

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.END as end__c
FROM
MobileApp_RT_Events__dlm
GROUP BY
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.

Statement What It Does

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

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

FROM

MobileApp_RT_Events__dlm

Use the MobileApp events streaming source.

GROUP BY

window(

MobileApp_RT_Events__dlm.dateTime__c,

'5 MINUTE'

),

MobileApp_RT_Events__dlm.AddToCartWeb_productId__c

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 GitHub 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.