modified code

an anonymous user · June 24, 2022
select * from 

(
Select zd_support_poc,zendesk_ticket_id,date(zd_created_date_timestamp) as zd_conv_date, zd_conv_month, zd_conv_week
,date(zd_solved_date) as zd_conv_close_date,zd_conversation_type
,zd_issue_category,'All' as zd_usersubtype
,zd_product_vertical,zd_source,zd_destination,zd_sme_pod,zd_premium_customer
,zd_priority,zd_ticket_status
,zd_reply_time_in_min, zd_full_resolution_time_in_min, zd_actual_resolution_time
,count(distinct(zd_conv.zendesk_ticket_id)) as total_conversations
,count(distinct(case when zd_conversation_type not like '%Invalid conversation%' then zd_conv.zendesk_ticket_id end)) as zd_valid_conversations
,count(distinct(case when zd_reply_time_in_min is not null then zd_conv.zendesk_ticket_id end )) as zd_replied_conversations
,count(distinct(case when zd_ticket_status = 'closed' then zd_conv.zendesk_ticket_id end)) as zd_closed_conversations
,count(distinct(case when zd_ticket_status = 'solved' then zd_conv.zendesk_ticket_id end)) as zd_solved_conversations
,count(distinct(case when zd_ticket_status = 'pending' then zd_conv.zendesk_ticket_id end)) as zd_pending_conversations
,count(distinct(case when zd_ticket_status = 'hold' then zd_conv.zendesk_ticket_id end)) as zd_conversations_on_hold
,count(distinct(case when zd_ticket_status = 'new' then zd_conv.zendesk_ticket_id end)) as zd_new_conversations
,count(distinct(case when zd_ticket_status = 'open' then zd_conv.zendesk_ticket_id end)) as zd_open_conversations
,count(distinct(case when zd_on_hold_time_in_min > 0 then zd_conv.zendesk_ticket_id end)) as zd_raised_to_on_call
,count(distinct(case when zd_conversation_type like '%issue%' and  zd_actual_resolution_time > 480 then zd_conv.zendesk_ticket_id end)) as zd_SLA_NOT_Followed_ISSUE
,count(distinct(case when zd_conversation_type like '%issue%' and zd_actual_resolution_time <= 480 then zd_conv.zendesk_ticket_id end)) as zd_SLA_Followed_ISSUE
,count(distinct(case when zd_no_of_reopens > 0 and zd_conversation_type like '%issue%' then zd_conv.zendesk_ticket_id end )) as zd_reopened_issues
,count( distinct( case when zd_reply_time_in_min is not null and zd_conversation_type like '%issue%'  then zd_conv.zendesk_ticket_id end)) as zd_Issue_count
, count( distinct(case when zd_reply_time_in_min is not null and zd_conversation_type like '%issue%'  and zd_actual_resolution_time < 480 then zd_conv.zendesk_ticket_id end)) as zd_Issue_solved_within_8_hr_actual
, count( distinct(case when zd_reply_time_in_min is not null and zd_conversation_type like '%issue%'  and zd_actual_resolution_time < 1440 then zd_conv.zendesk_ticket_id end)) as zd_Issue_solved_within_24_hr_actual
, count( distinct(case when zd_reply_time_in_min is not null and zd_conversation_type like '%issue%'  and zd_full_resolution_time_in_min < 480 then zd_conv.zendesk_ticket_id end)) as zd_Issue_solved_within_8_hr_overall
, count( distinct(case when zd_reply_time_in_min is not null and zd_conversation_type like '%issue%'  and zd_full_resolution_time_in_min < 1440 then zd_conv.zendesk_ticket_id end)) as zd_Issue_solved_within_24_hr_overall
, avg(zd_reply_time_in_min) as avg_ttfr
, count(distinct( case when zd_reply_time_in_min > 10 then zd_conv.zendesk_ticket_id end)) as zd_ttfr_sla_not_followed
, count(distinct( case when zd_reply_time_in_min < 10 then zd_conv.zendesk_ticket_id end)) as zd_ttfr_sla_followed
, avg(zd_actual_resolution_time) as zd_avg_ttr
From zendesk_conversations as zd_conv
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19
order by 2 desc
)

union

(
Select zd_support_poc,zendesk_ticket_id,date(zd_created_date_timestamp) as zd_conv_date, zd_conv_month, zd_conv_week
,date(zd_solved_date) as zd_conv_close_date,zd_conversation_type
,zd_issue_category,user_subtype as zd_usersubtype
,zd_product_vertical,zd_source,zd_destination,zd_sme_pod,zd_premium_customer
,zd_priority,zd_ticket_status
,zd_reply_time_in_min, zd_full_resolution_time_in_min, zd_actual_resolution_time
,count(distinct(zd_conv.zendesk_ticket_id)) as total_conversations
,count(distinct(case when zd_conversation_type not like '%Invalid conversation%' then zd_conv.zendesk_ticket_id end)) as zd_valid_conversations
,count(distinct(case when zd_reply_time_in_min is not null then zd_conv.zendesk_ticket_id end )) as zd_replied_conversations
,count(distinct(case when zd_ticket_status = 'closed' then zd_conv.zendesk_ticket_id end)) as zd_closed_conversations
,count(distinct(case when zd_ticket_status = 'solved' then zd_conv.zendesk_ticket_id end)) as zd_solved_conversations
,count(distinct(case when zd_ticket_status = 'pending' then zd_conv.zendesk_ticket_id end)) as zd_pending_conversations
,count(distinct(case when zd_ticket_status = 'hold' then zd_conv.zendesk_ticket_id end)) as zd_conversations_on_hold
,count(distinct(case when zd_ticket_status = 'new' then zd_conv.zendesk_ticket_id end)) as zd_new_conversations
,count(distinct(case when zd_ticket_status = 'open' then zd_conv.zendesk_ticket_id end)) as zd_open_conversations
,count(distinct(case when zd_on_hold_time_in_min > 0 then zd_conv.zendesk_ticket_id end)) as zd_raised_to_on_call
,count(distinct(case when zd_conversation_type like '%issue%' and  zd_actual_resolution_time > 480 then zd_conv.zendesk_ticket_id end)) as zd_SLA_NOT_Followed_ISSUE
,count(distinct(case when zd_conversation_type like '%issue%' and zd_actual_resolution_time <= 480 then zd_conv.zendesk_ticket_id end)) as zd_SLA_Followed_ISSUE
,count(distinct(case when zd_no_of_reopens > 0 and zd_conversation_type like '%issue%' then zd_conv.zendesk_ticket_id end )) as zd_reopened_issues
,count( distinct( case when zd_reply_time_in_min is not null and zd_conversation_type like '%issue%'  then zd_conv.zendesk_ticket_id end)) as zd_Issue_count
, count( distinct(case when zd_reply_time_in_min is not null and zd_conversation_type like '%issue%'  and zd_actual_resolution_time < 480 then zd_conv.zendesk_ticket_id end)) as zd_Issue_solved_within_8_hr_actual
, count( distinct(case when zd_reply_time_in_min is not null and zd_conversation_type like '%issue%'  and zd_actual_resolution_time < 1440 then zd_conv.zendesk_ticket_id end)) as zd_Issue_solved_within_24_hr_actual
, count( distinct(case when zd_reply_time_in_min is not null and zd_conversation_type like '%issue%'  and zd_full_resolution_time_in_min < 480 then zd_conv.zendesk_ticket_id end)) as zd_Issue_solved_within_8_hr_overall
, count( distinct(case when zd_reply_time_in_min is not null and zd_conversation_type like '%issue%'  and zd_full_resolution_time_in_min < 1440 then zd_conv.zendesk_ticket_id end)) as zd_Issue_solved_within_24_hr_overall
, avg(zd_reply_time_in_min) as avg_ttfr
, count(distinct( case when zd_reply_time_in_min > 10 then zd_conv.zendesk_ticket_id end)) as zd_ttfr_sla_not_followed
, count(distinct( case when zd_reply_time_in_min < 10 then zd_conv.zendesk_ticket_id end)) as zd_ttfr_sla_followed
, avg(zd_actual_resolution_time) as zd_avg_ttr
From zendesk_conversations as zd_conv
group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19
order by 2 desc
)
Output
(Run the program to view its output)

Comments

Please sign up or log in to contribute to the discussion.