select 
    e._id as activity_id 
    , _created as ts 
    , JSON_EXTRACT_PATH_TEXT(e.invitee, 'email') as customer 
    
    , NULL as source_id 
    , NULL as source

    , CASE 
        WHEN JSON_EXTRACT_PATH_TEXT(e.invitee, 'is_reschedule') = 'True' 
            THEN 'rescheduled_meeting'
        WHEN JSON_EXTRACT_PATH_TEXT(e.invitee, 'canceled') = 'True' 
            THEN 'cancelled_meeting'
        ELSE 'scheduled_meeting' 

    END as activity 
        
    ,JSON_EXTRACT_PATH_TEXT(e.event_type, 'name') as feature_1 -- event name
    
    , nullif(
        CASE 
            WHEN JSON_EXTRACT_PATH_TEXT(e.invitee, 'canceled') <> 'True' 
                THEN JSON_EXTRACT_PATH_TEXT(e.event_type, 'kind')  
            ELSE JSON_EXTRACT_PATH_TEXT(e.invitee, 'cancel_reason') 
        END
    ,'') as feature_2 -- kind of event or cancelled reason

    , JSON_EXTRACT_PATH_TEXT(e.event, 'start_time')::TIMESTAMPTZ::TIMESTAMP as feature_3 -- canceler_name

    , NULL as revenue_impact 
    , NULL as link 
from calendly.events e 
where invitee is not null -- details about who signed up

select 
    e._id as activity_id 
    , JSON_EXTRACT_PATH_TEXT(e.event, 'start_time')::TIMESTAMPTZ::TIMESTAMP  as ts 
    , JSON_EXTRACT_PATH_TEXT(e.invitee, 'email') as customer 
    
    , NULL as source_id 
    , NULL as source

    , 'attended_meeting' as activity 
        
    ,JSON_EXTRACT_PATH_TEXT(e.event_type, 'name') as feature_1 -- event name
    
    , JSON_EXTRACT_PATH_TEXT(e.event_type, 'kind')  as feature_2 -- kind of event
   
   , DATE_DIFF('minutes', JSON_EXTRACT_PATH_TEXT(e.event, 'start_time')::TIMESTAMPTZ::TIMESTAMP,JSON_EXTRACT_PATH_TEXT(e.event, 'end_time')::TIMESTAMPTZ::TIMESTAMP) as feature_3 
    , NULL as revenue_impact 
    , NULL as link 
    
from calendly.events e 
where invitee is not null -- details about who signed up
    and JSON_EXTRACT_PATH_TEXT(e.event, 'start_time')::TIMESTAMPTZ::TIMESTAMP < SYSDATE
  and JSON_EXTRACT_PATH_TEXT(e.invitee, 'canceled') <> 'True'