SELECT
     t.id  AS "activity_id"
     , t.created_at  AS "ts"
     
     , 'zendesk'  AS "source"
     , t.submitter_id AS "source_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"
     
     , 'zendesk'  AS "source"
     , t.submitter_id AS "source_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 
    , 'zendesk' as source 
    , t.submitter_id as source_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'