support_analytics.sql

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