SELECT
	 t.id  AS "activity_id"
	 , t.created_at  AS "ts"

	 , t.submitter_id AS "anonymous_customer_id"
	 
	 , u.email  AS "customer"
	 
	 , 'submitted_ticket' as "activity"
	    
	 , t.subject ::varchar(250)  AS "feature_1" -- subject 
   , t.id as "feature_2" -- ticket_id 
	 , a.email as "feature_3" -- assigned_to

	 , NULL AS "revenue_impact"
	 , 'https://SUPPORTDOMAIN.zendesk.com/agent/tickets/' || t.id  AS "link"
	 
FROM zendesk.ticket AS t
JOIN zendesk.user AS u
	ON ( u.id = t.submitter_id )
JOIN zendesk.user AS a
	ON ( a.id = t.assignee_id )

WHERE t.subject <> 'SCRUBBED'

SELECT
	 t.id  AS activity_id
	 , t.created_at  AS ts
	
	 , t.submitter_id AS anonymous_customer_id
	 
	 , u.email  AS customer
	 
	 , 'submitted_ticket' as  activity
	    
	 , t.subject ::varchar(250)  AS feature_1 -- subject 
   , t.id as feature_2 -- ticket_id 
	 , a.email as feature_3 -- assigned_to

	 , NULL AS revenue_impact
	 , 'https://SUPPORTDOMAIN.zendesk.com/agent/tickets/' || t.id  AS link
	 
FROM zendesk.ticket AS t
JOIN zendesk.user AS u
	ON ( u.id = t.submitter_id )
JOIN zendesk.user AS a
	ON ( a.id = t.assignee_id )

WHERE t.subject <> 'SCRUBBED'

SELECT
	 e.id  AS "activity_id"
	 , e."timestamp"  AS "ts"
	 
	 , t.submitter_id AS "anonymous_customer_id"
	 
	 , u.email  AS customer
	 
	 , CASE 
	        WHEN e.event_type = 'Comment' THEN 'commented_ticket' 
	        WHEN e.event_type = 'Change' and e.status = 'open' and e.previous_value in ('closed', 'solved') THEN 'reopened_ticket'
	        WHEN e.event_type = 'Change' and e.status in ('closed', 'solved') THEN e.status || '_ticket' 
	        
	   END as  "activity"
	    
	 , t.subject ::varchar(250)  AS "feature_1"
	 , e.ticket_event_via  AS "feature_2"
	 , a.email  AS "feature_3"
	 
	 , NULL AS "revenue_impact"
	 , 'https://SUPPORTDOMAIN.zendesk.com/agent/tickets/' || t.id  AS "link"
    
FROM zendesk.ticket_events AS e
JOIN zendesk.tickets AS t
	ON ( e.ticket_id = t.id and t.subject <> 'SCRUBBED' )
JOIN zendesk.users AS u
	ON ( u.id = t.submitter_id )
JOIN zendesk.users AS a
	ON ( a.id = t.assignee_id )

WHERE 
    e.event_type =  'Comment' or 
    (e.event_type= 'Change' and e.status in  ('closed', 'solved')) or 
    (e.event_type= 'Change' and e.status = 'open' and previous_value in ('closed', 'solved') )

SELECT
	 e.id  AS activity_id
	 , e."timestamp"  AS ts
	
	 , t.submitter_id AS anonymous_customer_id
	 
	 , u.email  AS customer
	 
	 , CASE 
	        WHEN e.event_type = 'Comment' THEN 'commented_ticket' 
	        WHEN e.event_type = 'Change' and e.status = 'open' and e.previous_value in ('closed', 'solved') THEN 'reopened_ticket'
	        WHEN e.event_type = 'Change' and e.status in ('closed', 'solved') THEN e.status || '_ticket' 
	        
	   END as  activity
	    
	 , t.subject ::varchar(250)  AS feature_1
	 , e.ticket_event_via  AS feature_2
	 , a.email  AS feature_3
	 
	 , NULL AS revenue_impact
	 , 'https://SUPPORTDOMAIN.zendesk.com/agent/tickets/' || t.id  AS link
    
FROM zendesk.ticket_events AS e
JOIN zendesk.tickets AS t
	ON ( e.ticket_id = t.id and t.subject <> 'SCRUBBED' )
JOIN zendesk.users AS u
	ON ( u.id = t.submitter_id )
JOIN zendesk.users AS a
	ON ( a.id = t.assignee_id )

WHERE 
    e.event_type =  'Comment' or 
    (e.event_type= 'Change' and e.status in  ('closed', 'solved')) or 
    (e.event_type= 'Change' and e.status = 'open' and previous_value in ('closed', 'solved') )

select 
    s.id as "activity_id"
    , s.created_at as "ts"

    , t.submitter_id as "anonymous_customer_id"
    
    , u.email as "customer"
    , 'rated_support_experience' as "activity"
    
    , s.score as "feature_1" --rating
    , s.reason as "feature_2" -- reason
    , NULL as "feature_3"
    
    , NULL as "revenue_impact" 
    -- link to the ticket 
    , 'https://COMPANYDOMAIN.zendesk.com/agent/tickets/' || t.id  AS "link"

from zendesk.satisfaction_rating s 
inner join zendesk.ticket t 
    on t.id = s.ticket_id 
JOIN zendesk.user AS u
	ON ( u.id = t.submitter_id )

where 
    score <> 'offered'

select 
    s.id as activity_id
    , s.created_at as ts 

    , t.submitter_id as anonymous_customer_id 
    
    , u.email as customer
    , 'rated_support_experience' as activity 
    
    , s.score as feature_1 --rating
    , s.reason as feature_2 -- reason
    , NULL as feature_3
    
    , NULL as revenue_impact 
    -- link to the ticket 
    , 'https://COMPANYDOMAIN.zendesk.com/agent/tickets/' || t.id  AS link

from zendesk.satisfaction_rating s 
inner join zendesk.ticket t 
    on t.id = s.ticket_id 
JOIN zendesk.user AS u
	ON ( u.id = t.submitter_id )

where 
    score <> 'offered'