Helpful SQL Queries

[#1]

This article provides helpful SQL queries and best practices for DataAccess. 

In this article:

• Join Events of Stitched and Non-Stitched Visitors
• INNER JOIN example
• Query Best Practices
• Additional Information

Join Events of Stitched and Non-Stitched Visitors

This example shows a sample query on how to join events from EventDB to AudienceDB to be able to import data into Tableau (to join stitched and non-stitched visitors).

Sample Query

See the following query example:

SELECT * FROM

(SELECT visitor_replaces.visitor_replaces_id, events__all_events.*

FROM yourdatabase__main.events__all_events 

 LEFT OUTER JOIN yourdatabase__main.visitors ON events__all_events.visitorid = visitors.visitor_id

 LEFT OUTER JOIN yourdatabase__main.visitor_replaces ON events__all_events.visitorid = visitor_replaces.visitor_replaces_id

 WHERE (visitors.visitor_id is not null OR 

 visitor_replaces.visitor_replaces_id is not null)

 AND visitors.visitor_id = '015fb7cf770300b09fc01fa4a2680406c0017064008a0' 

 )

 order by eventtime asc LIMIT 100

• To join events from EventDB to AudienceDB, you need to join events__x directly to the visitors table for non-stitched visitors or through the visitor_replaces table for those that have been stitched.
• This method accounts for visitor ids in events__all_events being in visitors or visitor_replaces.

INNER JOIN example

This example shows a sample query on how to join tables from AudienceDB and EventDB to be able to check the attributes on specific conditions in relation to the offline file import data.

Sample Query

See the following query example:

SELECT DISTINCT "visitor - id", "visitor - date - last call center date", 
"event - time", "event - udo - off_callctr_cdr_date"

FROM visitors_view_normalized 

INNER JOIN events_view__call_center_data on "visitor - id" = "event - visitor id"

WHERE "visit - day" > '2020-09-02' AND "event - day" > '2020-09-02'

AND "event - visitor id" like '__yourdatabase_main__5248_%' 

AND
(("event - udo - off_callctr_call_type" ilike 'outbound' AND "event - udo - off_callctr_media" ilike 'epcallback')
OR
("event - udo - off_callctr_pl_skills_category" ilike 'pl service skills' AND "event - udo - off_callctr_call_type" ilike 'inbound'))

1. Replace "yourdatabase" with the desired database name. 
2. Replace "events_view__call_center_data" with your desired table name

Query Best Practices

• Select specific columns. SELECT * is inefficient and can return much more data than required. It degrades the performance and the query can take longer to execute.
• Add a predicate filter (WHERE clause) where necessary.
• Specify a date range. 
• Use the LIMIT clause to select a limited number of records.

Additional Information

The following TLC articles provide additional information regarding DataAccess:

• Connecting to EventDB and AudienceDB with SQLWorkbench/J
• DataAccess: How to get a holistic view on every visitor joining AudienceDB and EventDB
• EventDB/AudienceDB Best Practices for Writing Queries