gregorynicholas · June 15, 2018 03:50
diff --git a/chartio_vs_amplitude_job_count_query.sql b/chartio_vs_amplitude_job_count_query.sql
 with data as (
    select distinct 
    	 case when creation_client = 'customer.api' then true else false end as is_api_job,
           -- j.customer_uuid
           j.job_uuid
         -- , j.job_date_local
         , date_trunc('hour',job_date_local) as hour
         -- , date_trunc('day',job_date_local) as day
         , date_trunc('hour',job_date_utc) as utc_hour
         -- , j.job_date_utc
         -- , end_date_local
         , j.market
         -- , j.job_id
         , j.is_promo
         -- , j.courier_uuid
         , j.product_type
         , j.customer_price_multiplier > 1.0 AND j.price_multiplier_source = 'market' as blitz
         , j.timezone
         , j.fsm_state
         , j.seconds_late between -1200 and 0 as ontime
         , j.is_late as late
         -- , j.attempts as attempt
         -- , j.courier_issue_reasons
         -- , j.order_escalation_reasons
         , CASE WHEN j.zendesk_issue_reasons like '%cs_primary%' THEN j.zendesk_issue_reasons END as zendesk_issue_reasons
         , j.hcs_reason
         --, lead(job_date_local,1) over (partition by j.customer_uuid order by j.job_date_local)
         , CASE WHEN fsm_state = 'DidCompleteDropoff'
              THEN 'completed'
              WHEN fsm_state in ('DidAdminCancel','DidCustomerCancel')
              THEN 'canceled'
              WHEN fsm_state in ('DidDispatchRequestException','DidDispatchCancel')
              THEN 'rejected'
              ELSE NULL END 
             as result
         , CASE WHEN fsm_state in ('DidAdminCancel','DidCustomerCancel')
                THEN 
  
        -- Tier 1 HCS Reasons
       case when j.fraud_canceled OR jh.fraud_canceled
            THEN 'Fraud'
            when j.hcs_reason like '%merchant_too_busy%'
            THEN 'Merchant Busy - Caller'
            WHEN j.hcs_reason like '%merchant_closed_workbench%'
            THEN 'Merchant Closed - Caller'
            WHEN j.hcs_reason like '%merchant_closed%'
            THEN 'Merchant Closed - Postmate'
            WHEN j.hcs_reason like '%merchant_permanently_closed%'
            THEN 'Merchant Closed Permanently - Caller'
            WHEN j.hcs_reason like '%merchant_wrong_hours%'
            THEN 'Merchant Closed Wrong Hours - Caller'
            WHEN j.hcs_reason like '%merchant_refused_order%'
                 AND j.order_channel = 'in_store'
            THEN 'Merchant Refused - Postmate'
            WHEN j.hcs_reason like '%merchant_refused_order%'
                 AND j.order_channel != 'in_store'
            THEN 'Merchant Refused - Caller'
            WHEN j.hcs_reason like '%merchant_wrong_number%'
            THEN 'Merchant Wrong Number - Caller'
            WHEN j.hcs_reason like '%merchant_wrong_address%'
            THEN 'Merchant Wrong Address - Postmate'
            WHEN j.hcs_reason like '%merchant_payment_cash_only%'
            THEN 'Merchant Cash Only - Postmate'
            WHEN j.hcs_reason like '%merchant_payment_machine_broken%'
            THEN 'Merchant Payment Problem - Postmate'
            WHEN j.hcs_reason like '%merchant_website_error%'
            THEN 'Merchant Website Error - Caller'
            WHEN j.hcs_reason like '%bad_cot_cancel%'
            THEN 'Merchant no phone orders - Caller'
            WHEN j.hcs_reason like '%signal_reassign_imminent%'
            THEN 'Fleet Reassigned COT Imm - Postmate'
            WHEN j.hcs_reason like '%reassign_ordered_imminent%'
            THEN 'Fleet Reassigned Ordered - Postmate'
            WHEN j.hcs_reason like '%signal_reassign_excessive%'
            THEN 'Fleet Reassigned Excessive - Postmate'
            WHEN j.hcs_reason like '%signal_early_complete%'
            THEN 'Fleet Early Complete - Postmate'
            WHEN j.hcs_reason like '%signal_dispatch_fail%'
           and order_channel = 'in_store'
            THEN 'Fleet Dispatch Fail (COT) - none'
            WHEN j.hcs_reason like '%signal_dispatch_fail%'
           and order_channel != 'in_store'
            THEN 'Fleet Dispatch Fail (non-COT) - none'
            WHEN j.hcs_reason like '%signal_early_complete%'
            THEN 'Fleet Early Complete - Postmate'
            WHEN j.hcs_reason like '%Customer didn''t respond to substitution request%'
            THEN 'Buyer Sub No Response - none'
            WHEN j.hcs_reason like '%chipotle_no_cot%'
            THEN 'Merchant Chipotle API error - none'
            WHEN j.hcs_reason like '%courier_issue_order_damaged%'
            THEN 'Fleet Order Damaged - Postmate'
            WHEN j.hcs_reason like '%courier_issue_payment%'
            THEN 'Fleet Payment Issue - Postmate'
            WHEN j.hcs_reason like '%personal_emergency%'
            THEN 'Fleet Reassigned Dropoff - Postmate'
            WHEN j.hcs_reason like '%restricted_items%'
            THEN 'Buyer Restricted Items - Postmate'
      
        -- capture quick cancels prior to other reasons
            WHEN j.hcs_reason like '%cx_cancel%'
               AND j.didacceptrequest notnull
               AND datediff(s,j.didacceptrequest,j.end_date_utc) < 180
            THEN 'Buyer Quick Cancel - Buyer'
            WHEN j.hcs_reason like '%cx_cancel_other, cancellation state%'
            THEN 'Buyer Cancel Other - Buyer'
            WHEN j.hcs_reason like '%cx_cancel_changed_my_mind, cancellation state%'
            THEN 'Buyer Cancel Changed Mind - Buyer'
            WHEN j.hcs_reason like '%cx_cancel_wont_be_there, cancellation state%'
            THEN 'Buyer Cancel Wont Be There - Buyer'

        -- Tier 2 Reliable job notes
            WHEN jh.customer_cancel_sub
            THEN 'Buyer Sub Cancel - Buyer'
            WHEN jh.tablet_unconfirmed
            THEN 'Merchant Unconfirmed - none'
            WHEN jh.dispatch_fail
            THEN 'Fleet Dispatch Fail - none'
            
        -- 5.19.17 -- added new Internal cancel hashtags
            WHEN jh.cancel_hashtag like '%#DIDITWRONG%'
            THEN 'Buyer API User Error - CS'
            WHEN jh.cancel_hashtag like '%#API%#MOWC%'
            THEN 'Buyer API MX Requested - CS'
            WHEN jh.cancel_hashtag like '%#NOPM%'
            THEN 'Buyer API Dispatch Fail - CS'
            WHEN jh.cancel_hashtag like '%#MOWC%' -- this must come AFTER 'Buyer API MX Requested - CS' in this query
            THEN 'Merchant Other - CS'
            WHEN jh.cancel_hashtag like '%#CAPACITY%'
            THEN 'Merchant At Capacity - CS'
            WHEN jh.cancel_hashtag like '%#CLOSED%'
            THEN 'Merchant Closed - CS'
            WHEN jh.cancel_hashtag like '%#CANCELERROR%'
            THEN 'Buyer Unable to Cancel in App - CS'
            WHEN jh.cancel_hashtag like '%#CXUNWILLING%'
            THEN 'Buyer Requests Admin Cancel - CS'
            WHEN jh.cancel_hashtag like '%#DROPOFF%'
            THEN 'Buyer Change Dropoff - CS'
            WHEN jh.cancel_hashtag like '%#DUPLICATE%'
            THEN 'Buyer Duplicate Order - CS'
            WHEN jh.cancel_hashtag like '%#VLATE%'
            THEN 'Very Late - CS'
            WHEN jh.cancel_hashtag like '%#IU (Item Unavailable%'
            THEN 'Merchant Item Unavail. - CS'
            WHEN jh.cancel_hashtag like '%#MENUOUTDATED%'
            THEN 'Merchant Menu Outdated - CS'
            WHEN jh.cancel_hashtag like '%#TIME%'
            THEN 'Merchant Time-based Menu - CS'
            WHEN jh.cancel_hashtag like '%#SPECIAL%'
            THEN 'Merchant Specialized Menu - CS'
            WHEN jh.cancel_hashtag like '%#MIA%'
            THEN 'Fleet Courier Unreachable - CS'
            WHEN jh.cancel_hashtag like '%#MNA%'
            THEN 'Merchant Unreachable - CS'
            WHEN jh.cancel_hashtag like '%#MOWC%'
            THEN 'Merchant Reuqested Cancel - CS'
            WHEN jh.cancel_hashtag like '%#OG (Option Groups)%'
            THEN 'Merchant Option Groups - CS'
            WHEN jh.cancel_hashtag like '%#DAMAGED%'
            THEN 'Fleet Damaged Goods - CS'
            WHEN jh.cancel_hashtag like '%#PE (Personal Emergency%'
            THEN 'Fleet Personal Emergency - CS'
            WHEN jh.cancel_hashtag like '%#PEX%'
                 OR jh.cancel_hashtag like '%#LOS_POSTMATES_PREPAID_CARD%'
            THEN 'Fleet Pex - CS'
            WHEN jh.cancel_hashtag like '%#TOOFAR%'
            THEN 'Fleet Too Far - CS'
            WHEN jh.cancel_hashtag like '%#TRANSPORT%'
            THEN 'Fleet Vehicle Capability - CS'
            WHEN jh.cancel_hashtag like '%#REFUSED%'
            THEN 'Merchant Refused - CS'
            WHEN jh.cancel_hashtag like '%#WHPI%'
            THEN 'Merchant Special Instr Price Adj Necessary - CS'
            WHEN jh.cancel_hashtag like '%#WNHPI%'
            THEN 'Merchant Special Instr - CS'
            WHEN jh.cancel_hashtag like '%#RESTRICTED%'
            THEN 'Buyer Restricted Items - CS'
            WHEN jh.cancel_hashtag like '%#PMNOCANCEL%'
            THEN 'Fleet Requests Cancel - CS'


          -- 5.31.17 Added new workbench menu options
            -- WHEN ej.drill_down like '%website_down%'
            -- THEN 'Merchant Website Down - Caller'
            -- none of this will matter, because it is handled by HCS. It would need to go above HCS. Is that what we want?

            -- system errors
            WHEN jh.cannot_dispatch_dupe
            THEN 'Error Duplicate Job Created - none'
            WHEN jh.cannot_dispatch_place_closed
            THEN 'Error Place Closed - none'
            WHEN j.customer_id = 6070785
            THEN 'Error Return Job - none'

            WHEN j.last_transition = 'DidAcceptRequest'
               and datediff(s,j.didacceptrequest,coalesce(j.end_date_utc,j.didacceptrequest + interval '1 day')) < 180
            THEN 'Buyer Quick Cancel - Buyer'

          -- 5.31.17 Removing all the trash sorting. Putting it in a single bucket. They should be tagged appropriately with the new CS cancel menus.
          -- Tier 3 Less reliable signals
            -- WHEN zendesk_issue_reasons like '%merchant_closed%'
            -- THEN 'Merchant Closed - CS (Buyer)'
            -- WHEN zendesk_issue_reasons like '%item_unavailable%'
            -- THEN 'Merchant Item Unavailable - CS (Buyer)'
            -- WHEN zendesk_issue_reasons like '%special_instructions%'
            -- THEN 'Merchant Special Instr. - CS (Buyer)'
            -- WHEN zendesk_issue_reasons like '%failed_dispatch%'
            -- THEN 'Fleet Dispatch Fail - CS (Buyer)'
            -- WHEN order_escalation_reasons like '%sub%'
            -- THEN 'Buyer Sub Cancel - CS (Buyer)'
            -- WHEN courier_issue_reasons notnull
            --    and fsm_state = 'DidAdminCancel'
            -- THEN 'Fleet ? - CS (Fleet)'
            -- WHEN zendesk_issue_reasons notnull
            -- THEN '? ? - CS (Buyer)'

          -- Tier 4 garbage
            -- WHEN j.is_late 
            --    OR datediff(s,j.didacceptrequest,coalesce(j.end_date_utc,j.didacceptrequest + interval '1 day')) > 600
            --     AND j.last_transition = 'DidAcceptRequest'
            -- THEN 'Late'
            WHEN fsm_state = 'DidCustomerCancel'
               AND creation_client = 'customer.api'
            THEN 'Buyer Cancel API - Buyer'
            WHEN fsm_state = 'DidCustomerCancel'
            THEN 'Buyer Cancel Other (non-HCS) - Buyer' -- a few jobs daily don't register the job note for hcs
            WHEN courier_issue_reasons like '%auto_dropoff%'
            THEN 'Fleet Issue in Dropoff - Postmate'
            WHEN jh.fraud_screened
                 and fsm_state = 'DidAdminCancel' -- no other reason for cancellation and it was flagged. 
            THEN 'Fraud'
            WHEN fsm_state = 'DidAdminCancel'
            THEN '?'
            END END
          as cause

      from reporting.jobs j
      left join (select jh.job_id
              , bool_or(case when description ilike '%DoDispatchRequest FAILED%' 
                                  and description ilike '%identical job was recently dispatched%'
                             then true else false end) as cannot_dispatch_dupe
              , bool_or(case when description ilike '%DoDispatchRequest FAILED%' 
                                  and description ilike '% is closed"%'
                             then true else false end) as cannot_dispatch_place_closed
              , listagg(CASE WHEN description like '%ancel reason:%'
                    and action = 'admin_cancel'
                 THEN substring(jh.description from position('ancel reason:' IN jh.description))
                 END, ' -- ') as cancel_hashtag
              , bool_or(case when description ilike '%#fraud%' 
                        OR description ilike '%Pegasus Auto-cancel%'
                        OR description ilike 'BDA%'
                        OR (description ilike '%DoDispatchRequest FAILED%' 
                                  and description ilike '%cannot_dispatch_job%')
                  then true else false end) as fraud_canceled
              , bool_or(case when description ilike '%Flagged by pegasus%'
                             then true else false end) as fraud_screened
              , bool_or(case when description ilike '%customer_cancel_sub%' then true else false end) as customer_cancel_sub
              , bool_or(case when description ilike '%Unconfirmed Order Timeout%'
                        then true else false end) as tablet_unconfirmed
              , bool_or(case when description ilike '%signal_dispatch_fail%' then true else false end) as dispatch_fail
           from etl.core_jobhistory as jh
          where (description ilike '%fraud%'
               OR description ilike 'BDA%'
               OR description ilike '%Pegasus Auto-cancel%'
               OR description ilike '%Flagged by pegasus%'
               OR description ilike '%customer_cancel_sub%'
               or description ilike '%signal_dispatch_fail%'
               OR description ilike '%Unconfirmed Order Timeout%'
               OR description ilike '%DoDispatchRequest FAILED%'
               OR description like '%ancel reason:%')
             and description NOT like '%Issue Closed%'
          group by job_id) as jh using (job_id)
     where charge_state != 'auth_failed'
       and fsm_state in ('DidAdminCancel','DidCustomerCancel','DidCompleteDropoff','DidDispatchCancel','DidDispatchRequestException')
 )

 select sum(total_requests) as s_total_requests, sum(accepted) as s_accepted, sum(unaccepted) as s_unaccepted, sum(completed) as s_completed, sum(ontime) as s_ontime, sum(canceled) as s_canceled, sum(fraud_canceled) as s_fraud_canceled, is_api_job from (
 	select market,
       hour,
       timezone,
       utc_hour,
       product_type in ('plus','unlimited') as is_plus,
       sum((cause = 'Fraud')::int) as fraud_canceled,

       sum((cause isnull OR cause != 'Fraud')::int) as total_requests,

       sum(((cause isnull OR cause != 'Fraud') and result != 'rejected')::int) as accepted,
       sum(((cause isnull OR cause != 'Fraud') and result = 'rejected')::int) as unaccepted,

       sum((result = 'completed')::int) as completed,
       sum(((cause isnull OR cause != 'Fraud') and result != 'completed')::int) as incomplete,

       sum((zendesk_issue_reasons isnull and result = 'completed' and ontime)::int) as issue_free,
       sum((zendesk_issue_reasons notnull and result = 'completed' and ontime)::int) as not_issue_free,
       
       sum((ontime and result = 'completed')::int) as ontime,
       sum((late and result = 'completed')::int) as late,
       sum((NOT late and NOT ontime and result = 'completed')::int) as early,

       sum(((cause isnull OR cause != 'Fraud') and result = 'canceled')::int) as canceled,
     sum(((cause isnull OR cause != 'Fraud') and result = 'canceled' and hcs_reason notnull)::int) as auto_canceled,
     sum(((cause isnull OR cause != 'Fraud') and result = 'canceled' and hcs_reason isnull)::int) as manual_canceled,
     
 
       -- Teams
       sum(((cause isnull OR cause != 'Fraud') and cause like 'Fleet%')::int) as fleet,
       sum(((cause isnull OR cause != 'Fraud') and cause like 'Buyer%')::int) as buyer,
       sum(((cause isnull OR cause != 'Fraud') and cause like 'Merchant%')::int) as merchant,
       sum(((cause isnull OR cause != 'Fraud') and cause not like 'Fleet%'
                           and cause not like 'Buyer%'
                           and cause not like 'Merchant%')::int) as uncategorized,
 
       -- Origins
       sum(((cause isnull OR cause != 'Fraud') and cause like '%Postmate')::int) as origin_postmate,
       sum(((cause isnull OR cause != 'Fraud') and cause like '%Buyer')::int) as origin_buyer,
       sum(((cause isnull OR cause != 'Fraud') and cause like '%none')::int) as origin_none,
       sum(((cause isnull OR cause != 'Fraud') and cause like '%Caller')::int) as origin_caller,
       sum(((cause isnull OR cause != 'Fraud') and cause like '%CS%')::int) as origin_cs,

       -- Reasons
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Busy - Caller')::int) as merchant_busy_caller,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Closed - Caller')::int) as merchant_closed_caller,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Closed - Postmate')::int) as merchant_closed_fleet,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Closed Permanently - Caller')::int) as merchant_closed_permanent_caller,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Closed Wrong Hours - Caller')::int) as merchant_closed_wrong_hours_caller,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Refused - Postmate')::int) as merchant_refused_fleet,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Refused - Caller')::int) as merchant_refused_caller,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Wrong Number - Caller')::int) as merchant_wrong_number_caller,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Wrong Address - Postmate')::int) as merchant_wrong_address_fleet,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Cash Only - Postmate')::int) as merchant_cash_only_fleet,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Payment Problem - Postmate')::int) as merchant_payment_problem_fleet,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Website Error - Caller')::int) as merchant_website_error_caller,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant no phone orders - Caller')::int) as merchant_no_phone_orders_caller,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Fleet Reassigned COT Imm - Postmate')::int) as fleet_reassign_cot_imm_fleet,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Fleet Reassigned Ordered - Postmate')::int) as fleet_reassign_ordered_fleet,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Fleet Reassigned Excessive - Postmate')::int) as fleet_reassign_excessive_fleet,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Fleet Early Complete - Postmate')::int) as fleet_early_complete_fleet,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Fleet Dispatch Fail - auto')::int) as fleet_dispatch_fail_none,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Buyer Sub No Response - auto')::int) as buyer_sub_no_response_none,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Buyer Cancel Other - Buyer')::int) as buyer_other_buyer,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Buyer Cancel Changed Mind - Buyer')::int) as buyer_changed_mind_buyer,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Buyer Cancel Wont Be There - Buyer')::int) as buyer_not_there_buyer,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Buyer Sub Cancel - Buyer')::int) as buyer_sub_cancel_buyer,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Unconfirmed - Merchant')::int) as merchant_unconfirmed_tablet_none,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Buyer Quick Cancel - Buyer')::int) as buyer_quick_cancel_buyer,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Closed - CS (Buyer)')::int) as merchant_closed_cs,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Item Unavailable - CS (Buyer)')::int) as merchant_item_unavail_cs,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Special Instr. - CS (Buyer)')::int) as merchant_special_instr_cs,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Fleet Dispatch Fail - CS (Buyer)')::int) as fleet_dispatch_fail_cs,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Buyer Sub Cancel - CS (Buyer)')::int) as buyer_sub_cancel_cs,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Fleet ? - CS (Fleet)')::int) as fleet_unknown_cs,
       sum(((cause isnull OR cause != 'Fraud') and cause = '? ? - CS (Buyer)')::int) as unknown_misc_ZD_issue_cs,
       sum(((cause isnull OR cause != 'Fraud') and fsm_state in ('DidAdminCancel','DidCustomerCancel') and cause = 'Late')::int) as late,
       sum(((cause isnull OR cause != 'Fraud') and cause = 'Buyer Cancel ? - Buyer')::int) as buyer_unknown_buyer,
       sum(((cause isnull OR cause != 'Fraud') and cause = '? - CS')::int) as unknown_cs,

       sum(((cause isnull OR cause != 'Fraud') and is_promo)::int) as promo_count,
       sum(((cause isnull OR cause != 'Fraud') and blitz)::int) as blitz_count,
       is_api_job

 from data
 group by 1,2,3,4,5,is_api_job
 order by 2 desc, 1)
 where convert_timezone('US/Pacific', utc_hour) >= '6-19-2017' and convert_timezone('US/Pacific', utc_hour) < '6-20-2017'::timestamp - interval '5 seconds' group by is_api_job;
	with data as (
	select distinct
	case when creation_client = 'customer.api' then true else false end as is_api_job,
	-- j.customer_uuid
	j.job_uuid
	-- , j.job_date_local
	, date_trunc('hour',job_date_local) as hour
	-- , date_trunc('day',job_date_local) as day
	, date_trunc('hour',job_date_utc) as utc_hour
	-- , j.job_date_utc
	-- , end_date_local
	, j.market
	-- , j.job_id
	, j.is_promo
	-- , j.courier_uuid
	, j.product_type
	, j.customer_price_multiplier > 1.0 AND j.price_multiplier_source = 'market' as blitz
	, j.timezone
	, j.fsm_state
	, j.seconds_late between -1200 and 0 as ontime
	, j.is_late as late
	-- , j.attempts as attempt
	-- , j.courier_issue_reasons
	-- , j.order_escalation_reasons
	, CASE WHEN j.zendesk_issue_reasons like '%cs_primary%' THEN j.zendesk_issue_reasons END as zendesk_issue_reasons
	, j.hcs_reason
	--, lead(job_date_local,1) over (partition by j.customer_uuid order by j.job_date_local)
	, CASE WHEN fsm_state = 'DidCompleteDropoff'
	THEN 'completed'
	WHEN fsm_state in ('DidAdminCancel','DidCustomerCancel')
	THEN 'canceled'
	WHEN fsm_state in ('DidDispatchRequestException','DidDispatchCancel')
	THEN 'rejected'
	ELSE NULL END
	as result
	, CASE WHEN fsm_state in ('DidAdminCancel','DidCustomerCancel')
	THEN

	-- Tier 1 HCS Reasons
	case when j.fraud_canceled OR jh.fraud_canceled
	THEN 'Fraud'
	when j.hcs_reason like '%merchant_too_busy%'
	THEN 'Merchant Busy - Caller'
	WHEN j.hcs_reason like '%merchant_closed_workbench%'
	THEN 'Merchant Closed - Caller'
	WHEN j.hcs_reason like '%merchant_closed%'
	THEN 'Merchant Closed - Postmate'
	WHEN j.hcs_reason like '%merchant_permanently_closed%'
	THEN 'Merchant Closed Permanently - Caller'
	WHEN j.hcs_reason like '%merchant_wrong_hours%'
	THEN 'Merchant Closed Wrong Hours - Caller'
	WHEN j.hcs_reason like '%merchant_refused_order%'
	AND j.order_channel = 'in_store'
	THEN 'Merchant Refused - Postmate'
	WHEN j.hcs_reason like '%merchant_refused_order%'
	AND j.order_channel != 'in_store'
	THEN 'Merchant Refused - Caller'
	WHEN j.hcs_reason like '%merchant_wrong_number%'
	THEN 'Merchant Wrong Number - Caller'
	WHEN j.hcs_reason like '%merchant_wrong_address%'
	THEN 'Merchant Wrong Address - Postmate'
	WHEN j.hcs_reason like '%merchant_payment_cash_only%'
	THEN 'Merchant Cash Only - Postmate'
	WHEN j.hcs_reason like '%merchant_payment_machine_broken%'
	THEN 'Merchant Payment Problem - Postmate'
	WHEN j.hcs_reason like '%merchant_website_error%'
	THEN 'Merchant Website Error - Caller'
	WHEN j.hcs_reason like '%bad_cot_cancel%'
	THEN 'Merchant no phone orders - Caller'
	WHEN j.hcs_reason like '%signal_reassign_imminent%'
	THEN 'Fleet Reassigned COT Imm - Postmate'
	WHEN j.hcs_reason like '%reassign_ordered_imminent%'
	THEN 'Fleet Reassigned Ordered - Postmate'
	WHEN j.hcs_reason like '%signal_reassign_excessive%'
	THEN 'Fleet Reassigned Excessive - Postmate'
	WHEN j.hcs_reason like '%signal_early_complete%'
	THEN 'Fleet Early Complete - Postmate'
	WHEN j.hcs_reason like '%signal_dispatch_fail%'
	and order_channel = 'in_store'
	THEN 'Fleet Dispatch Fail (COT) - none'
	WHEN j.hcs_reason like '%signal_dispatch_fail%'
	and order_channel != 'in_store'
	THEN 'Fleet Dispatch Fail (non-COT) - none'
	WHEN j.hcs_reason like '%signal_early_complete%'
	THEN 'Fleet Early Complete - Postmate'
	WHEN j.hcs_reason like '%Customer didn''t respond to substitution request%'
	THEN 'Buyer Sub No Response - none'
	WHEN j.hcs_reason like '%chipotle_no_cot%'
	THEN 'Merchant Chipotle API error - none'
	WHEN j.hcs_reason like '%courier_issue_order_damaged%'
	THEN 'Fleet Order Damaged - Postmate'
	WHEN j.hcs_reason like '%courier_issue_payment%'
	THEN 'Fleet Payment Issue - Postmate'
	WHEN j.hcs_reason like '%personal_emergency%'
	THEN 'Fleet Reassigned Dropoff - Postmate'
	WHEN j.hcs_reason like '%restricted_items%'
	THEN 'Buyer Restricted Items - Postmate'

	-- capture quick cancels prior to other reasons
	WHEN j.hcs_reason like '%cx_cancel%'
	AND j.didacceptrequest notnull
	AND datediff(s,j.didacceptrequest,j.end_date_utc) < 180
	THEN 'Buyer Quick Cancel - Buyer'
	WHEN j.hcs_reason like '%cx_cancel_other, cancellation state%'
	THEN 'Buyer Cancel Other - Buyer'
	WHEN j.hcs_reason like '%cx_cancel_changed_my_mind, cancellation state%'
	THEN 'Buyer Cancel Changed Mind - Buyer'
	WHEN j.hcs_reason like '%cx_cancel_wont_be_there, cancellation state%'
	THEN 'Buyer Cancel Wont Be There - Buyer'

	-- Tier 2 Reliable job notes
	WHEN jh.customer_cancel_sub
	THEN 'Buyer Sub Cancel - Buyer'
	WHEN jh.tablet_unconfirmed
	THEN 'Merchant Unconfirmed - none'
	WHEN jh.dispatch_fail
	THEN 'Fleet Dispatch Fail - none'

	-- 5.19.17 -- added new Internal cancel hashtags
	WHEN jh.cancel_hashtag like '%#DIDITWRONG%'
	THEN 'Buyer API User Error - CS'
	WHEN jh.cancel_hashtag like '%#API%#MOWC%'
	THEN 'Buyer API MX Requested - CS'
	WHEN jh.cancel_hashtag like '%#NOPM%'
	THEN 'Buyer API Dispatch Fail - CS'
	WHEN jh.cancel_hashtag like '%#MOWC%' -- this must come AFTER 'Buyer API MX Requested - CS' in this query
	THEN 'Merchant Other - CS'
	WHEN jh.cancel_hashtag like '%#CAPACITY%'
	THEN 'Merchant At Capacity - CS'
	WHEN jh.cancel_hashtag like '%#CLOSED%'
	THEN 'Merchant Closed - CS'
	WHEN jh.cancel_hashtag like '%#CANCELERROR%'
	THEN 'Buyer Unable to Cancel in App - CS'
	WHEN jh.cancel_hashtag like '%#CXUNWILLING%'
	THEN 'Buyer Requests Admin Cancel - CS'
	WHEN jh.cancel_hashtag like '%#DROPOFF%'
	THEN 'Buyer Change Dropoff - CS'
	WHEN jh.cancel_hashtag like '%#DUPLICATE%'
	THEN 'Buyer Duplicate Order - CS'
	WHEN jh.cancel_hashtag like '%#VLATE%'
	THEN 'Very Late - CS'
	WHEN jh.cancel_hashtag like '%#IU (Item Unavailable%'
	THEN 'Merchant Item Unavail. - CS'
	WHEN jh.cancel_hashtag like '%#MENUOUTDATED%'
	THEN 'Merchant Menu Outdated - CS'
	WHEN jh.cancel_hashtag like '%#TIME%'
	THEN 'Merchant Time-based Menu - CS'
	WHEN jh.cancel_hashtag like '%#SPECIAL%'
	THEN 'Merchant Specialized Menu - CS'
	WHEN jh.cancel_hashtag like '%#MIA%'
	THEN 'Fleet Courier Unreachable - CS'
	WHEN jh.cancel_hashtag like '%#MNA%'
	THEN 'Merchant Unreachable - CS'
	WHEN jh.cancel_hashtag like '%#MOWC%'
	THEN 'Merchant Reuqested Cancel - CS'
	WHEN jh.cancel_hashtag like '%#OG (Option Groups)%'
	THEN 'Merchant Option Groups - CS'
	WHEN jh.cancel_hashtag like '%#DAMAGED%'
	THEN 'Fleet Damaged Goods - CS'
	WHEN jh.cancel_hashtag like '%#PE (Personal Emergency%'
	THEN 'Fleet Personal Emergency - CS'
	WHEN jh.cancel_hashtag like '%#PEX%'
	OR jh.cancel_hashtag like '%#LOS_POSTMATES_PREPAID_CARD%'
	THEN 'Fleet Pex - CS'
	WHEN jh.cancel_hashtag like '%#TOOFAR%'
	THEN 'Fleet Too Far - CS'
	WHEN jh.cancel_hashtag like '%#TRANSPORT%'
	THEN 'Fleet Vehicle Capability - CS'
	WHEN jh.cancel_hashtag like '%#REFUSED%'
	THEN 'Merchant Refused - CS'
	WHEN jh.cancel_hashtag like '%#WHPI%'
	THEN 'Merchant Special Instr Price Adj Necessary - CS'
	WHEN jh.cancel_hashtag like '%#WNHPI%'
	THEN 'Merchant Special Instr - CS'
	WHEN jh.cancel_hashtag like '%#RESTRICTED%'
	THEN 'Buyer Restricted Items - CS'
	WHEN jh.cancel_hashtag like '%#PMNOCANCEL%'
	THEN 'Fleet Requests Cancel - CS'


	-- 5.31.17 Added new workbench menu options
	-- WHEN ej.drill_down like '%website_down%'
	-- THEN 'Merchant Website Down - Caller'
	-- none of this will matter, because it is handled by HCS. It would need to go above HCS. Is that what we want?

	-- system errors
	WHEN jh.cannot_dispatch_dupe
	THEN 'Error Duplicate Job Created - none'
	WHEN jh.cannot_dispatch_place_closed
	THEN 'Error Place Closed - none'
	WHEN j.customer_id = 6070785
	THEN 'Error Return Job - none'

	WHEN j.last_transition = 'DidAcceptRequest'
	and datediff(s,j.didacceptrequest,coalesce(j.end_date_utc,j.didacceptrequest + interval '1 day')) < 180
	THEN 'Buyer Quick Cancel - Buyer'

	-- 5.31.17 Removing all the trash sorting. Putting it in a single bucket. They should be tagged appropriately with the new CS cancel menus.
	-- Tier 3 Less reliable signals
	-- WHEN zendesk_issue_reasons like '%merchant_closed%'
	-- THEN 'Merchant Closed - CS (Buyer)'
	-- WHEN zendesk_issue_reasons like '%item_unavailable%'
	-- THEN 'Merchant Item Unavailable - CS (Buyer)'
	-- WHEN zendesk_issue_reasons like '%special_instructions%'
	-- THEN 'Merchant Special Instr. - CS (Buyer)'
	-- WHEN zendesk_issue_reasons like '%failed_dispatch%'
	-- THEN 'Fleet Dispatch Fail - CS (Buyer)'
	-- WHEN order_escalation_reasons like '%sub%'
	-- THEN 'Buyer Sub Cancel - CS (Buyer)'
	-- WHEN courier_issue_reasons notnull
	-- and fsm_state = 'DidAdminCancel'
	-- THEN 'Fleet ? - CS (Fleet)'
	-- WHEN zendesk_issue_reasons notnull
	-- THEN '? ? - CS (Buyer)'

	-- Tier 4 garbage
	-- WHEN j.is_late
	-- OR datediff(s,j.didacceptrequest,coalesce(j.end_date_utc,j.didacceptrequest + interval '1 day')) > 600
	-- AND j.last_transition = 'DidAcceptRequest'
	-- THEN 'Late'
	WHEN fsm_state = 'DidCustomerCancel'
	AND creation_client = 'customer.api'
	THEN 'Buyer Cancel API - Buyer'
	WHEN fsm_state = 'DidCustomerCancel'
	THEN 'Buyer Cancel Other (non-HCS) - Buyer' -- a few jobs daily don't register the job note for hcs
	WHEN courier_issue_reasons like '%auto_dropoff%'
	THEN 'Fleet Issue in Dropoff - Postmate'
	WHEN jh.fraud_screened
	and fsm_state = 'DidAdminCancel' -- no other reason for cancellation and it was flagged.
	THEN 'Fraud'
	WHEN fsm_state = 'DidAdminCancel'
	THEN '?'
	END END
	as cause

	from reporting.jobs j
	left join (select jh.job_id
	, bool_or(case when description ilike '%DoDispatchRequest FAILED%'
	and description ilike '%identical job was recently dispatched%'
	then true else false end) as cannot_dispatch_dupe
	, bool_or(case when description ilike '%DoDispatchRequest FAILED%'
	and description ilike '% is closed"%'
	then true else false end) as cannot_dispatch_place_closed
	, listagg(CASE WHEN description like '%ancel reason:%'
	and action = 'admin_cancel'
	THEN substring(jh.description from position('ancel reason:' IN jh.description))
	END, ' -- ') as cancel_hashtag
	, bool_or(case when description ilike '%#fraud%'
	OR description ilike '%Pegasus Auto-cancel%'
	OR description ilike 'BDA%'
	OR (description ilike '%DoDispatchRequest FAILED%'
	and description ilike '%cannot_dispatch_job%')
	then true else false end) as fraud_canceled
	, bool_or(case when description ilike '%Flagged by pegasus%'
	then true else false end) as fraud_screened
	, bool_or(case when description ilike '%customer_cancel_sub%' then true else false end) as customer_cancel_sub
	, bool_or(case when description ilike '%Unconfirmed Order Timeout%'
	then true else false end) as tablet_unconfirmed
	, bool_or(case when description ilike '%signal_dispatch_fail%' then true else false end) as dispatch_fail
	from etl.core_jobhistory as jh
	where (description ilike '%fraud%'
	OR description ilike 'BDA%'
	OR description ilike '%Pegasus Auto-cancel%'
	OR description ilike '%Flagged by pegasus%'
	OR description ilike '%customer_cancel_sub%'
	or description ilike '%signal_dispatch_fail%'
	OR description ilike '%Unconfirmed Order Timeout%'
	OR description ilike '%DoDispatchRequest FAILED%'
	OR description like '%ancel reason:%')
	and description NOT like '%Issue Closed%'
	group by job_id) as jh using (job_id)
	where charge_state != 'auth_failed'
	and fsm_state in ('DidAdminCancel','DidCustomerCancel','DidCompleteDropoff','DidDispatchCancel','DidDispatchRequestException')
	)

	select sum(total_requests) as s_total_requests, sum(accepted) as s_accepted, sum(unaccepted) as s_unaccepted, sum(completed) as s_completed, sum(ontime) as s_ontime, sum(canceled) as s_canceled, sum(fraud_canceled) as s_fraud_canceled, is_api_job from (
	select market,
	hour,
	timezone,
	utc_hour,
	product_type in ('plus','unlimited') as is_plus,
	sum((cause = 'Fraud')::int) as fraud_canceled,

	sum((cause isnull OR cause != 'Fraud')::int) as total_requests,

	sum(((cause isnull OR cause != 'Fraud') and result != 'rejected')::int) as accepted,
	sum(((cause isnull OR cause != 'Fraud') and result = 'rejected')::int) as unaccepted,

	sum((result = 'completed')::int) as completed,
	sum(((cause isnull OR cause != 'Fraud') and result != 'completed')::int) as incomplete,

	sum((zendesk_issue_reasons isnull and result = 'completed' and ontime)::int) as issue_free,
	sum((zendesk_issue_reasons notnull and result = 'completed' and ontime)::int) as not_issue_free,

	sum((ontime and result = 'completed')::int) as ontime,
	sum((late and result = 'completed')::int) as late,
	sum((NOT late and NOT ontime and result = 'completed')::int) as early,

	sum(((cause isnull OR cause != 'Fraud') and result = 'canceled')::int) as canceled,
	sum(((cause isnull OR cause != 'Fraud') and result = 'canceled' and hcs_reason notnull)::int) as auto_canceled,
	sum(((cause isnull OR cause != 'Fraud') and result = 'canceled' and hcs_reason isnull)::int) as manual_canceled,


	-- Teams
	sum(((cause isnull OR cause != 'Fraud') and cause like 'Fleet%')::int) as fleet,
	sum(((cause isnull OR cause != 'Fraud') and cause like 'Buyer%')::int) as buyer,
	sum(((cause isnull OR cause != 'Fraud') and cause like 'Merchant%')::int) as merchant,
	sum(((cause isnull OR cause != 'Fraud') and cause not like 'Fleet%'
	and cause not like 'Buyer%'
	and cause not like 'Merchant%')::int) as uncategorized,

	-- Origins
	sum(((cause isnull OR cause != 'Fraud') and cause like '%Postmate')::int) as origin_postmate,
	sum(((cause isnull OR cause != 'Fraud') and cause like '%Buyer')::int) as origin_buyer,
	sum(((cause isnull OR cause != 'Fraud') and cause like '%none')::int) as origin_none,
	sum(((cause isnull OR cause != 'Fraud') and cause like '%Caller')::int) as origin_caller,
	sum(((cause isnull OR cause != 'Fraud') and cause like '%CS%')::int) as origin_cs,

	-- Reasons
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Busy - Caller')::int) as merchant_busy_caller,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Closed - Caller')::int) as merchant_closed_caller,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Closed - Postmate')::int) as merchant_closed_fleet,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Closed Permanently - Caller')::int) as merchant_closed_permanent_caller,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Closed Wrong Hours - Caller')::int) as merchant_closed_wrong_hours_caller,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Refused - Postmate')::int) as merchant_refused_fleet,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Refused - Caller')::int) as merchant_refused_caller,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Wrong Number - Caller')::int) as merchant_wrong_number_caller,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Wrong Address - Postmate')::int) as merchant_wrong_address_fleet,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Cash Only - Postmate')::int) as merchant_cash_only_fleet,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Payment Problem - Postmate')::int) as merchant_payment_problem_fleet,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Website Error - Caller')::int) as merchant_website_error_caller,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant no phone orders - Caller')::int) as merchant_no_phone_orders_caller,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Fleet Reassigned COT Imm - Postmate')::int) as fleet_reassign_cot_imm_fleet,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Fleet Reassigned Ordered - Postmate')::int) as fleet_reassign_ordered_fleet,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Fleet Reassigned Excessive - Postmate')::int) as fleet_reassign_excessive_fleet,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Fleet Early Complete - Postmate')::int) as fleet_early_complete_fleet,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Fleet Dispatch Fail - auto')::int) as fleet_dispatch_fail_none,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Buyer Sub No Response - auto')::int) as buyer_sub_no_response_none,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Buyer Cancel Other - Buyer')::int) as buyer_other_buyer,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Buyer Cancel Changed Mind - Buyer')::int) as buyer_changed_mind_buyer,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Buyer Cancel Wont Be There - Buyer')::int) as buyer_not_there_buyer,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Buyer Sub Cancel - Buyer')::int) as buyer_sub_cancel_buyer,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Unconfirmed - Merchant')::int) as merchant_unconfirmed_tablet_none,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Buyer Quick Cancel - Buyer')::int) as buyer_quick_cancel_buyer,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Closed - CS (Buyer)')::int) as merchant_closed_cs,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Item Unavailable - CS (Buyer)')::int) as merchant_item_unavail_cs,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Merchant Special Instr. - CS (Buyer)')::int) as merchant_special_instr_cs,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Fleet Dispatch Fail - CS (Buyer)')::int) as fleet_dispatch_fail_cs,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Buyer Sub Cancel - CS (Buyer)')::int) as buyer_sub_cancel_cs,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Fleet ? - CS (Fleet)')::int) as fleet_unknown_cs,
	sum(((cause isnull OR cause != 'Fraud') and cause = '? ? - CS (Buyer)')::int) as unknown_misc_ZD_issue_cs,
	sum(((cause isnull OR cause != 'Fraud') and fsm_state in ('DidAdminCancel','DidCustomerCancel') and cause = 'Late')::int) as late,
	sum(((cause isnull OR cause != 'Fraud') and cause = 'Buyer Cancel ? - Buyer')::int) as buyer_unknown_buyer,
	sum(((cause isnull OR cause != 'Fraud') and cause = '? - CS')::int) as unknown_cs,

	sum(((cause isnull OR cause != 'Fraud') and is_promo)::int) as promo_count,
	sum(((cause isnull OR cause != 'Fraud') and blitz)::int) as blitz_count,
	is_api_job

	from data
	group by 1,2,3,4,5,is_api_job
	order by 2 desc, 1)
	where convert_timezone('US/Pacific', utc_hour) >= '6-19-2017' and convert_timezone('US/Pacific', utc_hour) < '6-20-2017'::timestamp - interval '5 seconds' group by is_api_job;