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'