Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
WITH sfdc AS (
-- Get whole Salesforce Information (This table is replaced every data)
SELECT
backendid__c as account_id,
subscription_status__c as subscription,
estimated_mrr__c as mrr,
region__c as region
FROM sfdc.raw_account
WHERE backendid__c is not null
),
users AS (
-- Get account spcefied data from own account database (This table is appended every data)
SELECT DISTINCT account_id, email
FROM mysql.users
WHERE TD_TIME_RANGE(time, TD_TIME_ADD(TD_SCHEDULED_TIME(), '-1d'), TD_SCHEDULED_TIME())
),
latest_tickets AS (
-- Get latest updated ticket per ticket's id
SELECT
max(time) as updated_time,
id as ticket_id,
requester_id as requester_id,
created_at
FROM
zendesk.tickets
group by 2,3,4
),
unique_zendesk_users AS (
-- requester_id in tickets is linked to this email.
SELECT distinct id as user_id, email
FROM zendesk.users
),
zd AS (
-- Join latest zendesk tickets and unique zenkdesk users
SELECT
latest_tickets.updated_time,
latest_tickets.ticket_id,
latest_tickets.created_at,
unique_zendesk_users.email as requester_email
FROM latest_tickets
JOIN unique_zendesk_users ON latest_tickets.requester_id = unique_zendesk_users.user_id
),
users_sfdc AS (
SELECT
users.account_id,
users.email,
users.environment,
sfdc.subscription_status,
sfdc.mrr,
sfdc.region
FROM users JOIN sfdc
ON users.account_id = sfdc.account_id
)
SELECT
CAST(TD_TIME_FORMAT(zd.updated_time, 'yyyy-MM-dd HH:mm:ss', 'UTC') as timestamp) as updated_at_utc,
CAST(TD_TIME_FORMAT(zd.updated_time, 'yyyy-MM-dd HH:mm:ss', 'JST') as timestamp) as updated_at_jst,
zd.ticket_id,
CAST(zd.created_at as timestamp) as created_at_utc,
zd.requester_email,
users_sfdc.account_id,
users_sfdc.subscription,
users_sfdc.mrr,
users_sfdc.region,
zd.tags
FROM zd JOIN users_sfdc
ON zd.requester_email = users_sfdc.email
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment