select 
    i.id as activity_id,
    i.received_at as ts,

    NULL as anonymous_customer_id,
    
    cc.email as customer,
    
    'paid_invoice' as activity,
    
    i.description as feature_1,
    ii.id as feature_2,
    NULL as feature_3,
    
    i.amount / 100.0 as revenue_impact,
    NULL as link

FROM stripe.charges i
JOIN stripe.customers cc
    on (i.customer_id = cc.id)
LEFT JOIN stripe.invoices ii 
    on (ii.id = i.invoice_id)
    
WHERE  i.status='succeeded'

select 
    i.id as activity_id,
    i.date as ts,
   
    NULL as anonymous_customer_id,
    
    cc.email as customer,
    
    CASE WHEN i.subtotal=0 then 'received_null_invoice'
    else 'received_invoice' end as activity,
    
    d.coupon_id as feature_1,
    NULL as feature_2,
    NULL as feature_3,
    
    i.total/100.0 as revenue_impact,
    NULL as link

FROM stripe.invoices i
LEFT OUTER JOIN stripe.discounts d
    on (d.id = i.discount_id)
JOIN stripe.customers cc
    on (i.customer_id = cc.id)
WHERE i.subtotal>0

SELECT 
    s.id as activity_id,
    GREATEST(s.trial_end, s.start) as ts,
   
    NULL as anonymous_customer_id,
    
    c.email as customer,
    
    'started_subscription' as activity,
    
    p.nickname as feature_1, -- plan name
    cp.name as feature_2, -- coupon
    p.tiers_mode as feature_3,
    
    p.amount /100.0 as revenue_impact,
    NULL as link

FROM ft_stripe.subscription s
join ft_stripe.subscription_discount sd 
    on (sd.subscription_id = s.id)
join ft_stripe.coupon cp 
    on (co.id = sd.coupon_id )
join ft_stripe.subscription_item si 
    on (si.subscription_id = s.id )
join ft_stripe.plan p 
    on (si.plan_id = p.id )
join ft_stripe.customer c 
    on (c.id = s.customer_id)
    
where (s.canceled_at is null or s.trial_start is NULL or s.canceled_at > s.trial_end)
 -- handle the case where the user canceled during the trial

SELECT 
    s.id as activity_id,
    s.ended_at as ts,

    NULL as anonymous_customer_id,
    
    c.email as customer,
    
    'ended_subscription' as activity,
    
    p.nickname as feature_1, -- plan name
    cp.name as feature_2, -- coupon
    p.tiers_mode as feature_3,
    
    p.amount /100.0 as revenue_impact,
    NULL as link

FROM ft_stripe.subscription s
join ft_stripe.subscription_discount sd 
    on (sd.subscription_id = s.id)
join ft_stripe.coupon cp 
    on (co.id = sd.coupon_id )
join ft_stripe.subscription_item si 
    on (si.subscription_id = s.id )
join ft_stripe.plan p 
    on (si.plan_id = p.id )
join ft_stripe.customer c 
    on (c.id = s.customer_id)
    
where (s.canceled_at is null or s.trial_start is NULL or s.canceled_at > s.trial_end)
 -- handle the case where the user canceled during the trial
 	and s.ended_at is not null

SELECT 
    s.id as activity_id,
    s.cancelled_at as ts,
    
    NULL as anonymous_customer_id,
    
    c.email as customer,
    
    'ended_subscription' as activity,
    
    p.nickname as feature_1, -- plan name
    cp.name as feature_2, -- coupon
    p.tiers_mode as feature_3,
    
    p.amount /100.0 as revenue_impact,
    NULL as link

FROM ft_stripe.subscription s
join ft_stripe.subscription_discount sd 
    on (sd.subscription_id = s.id)
join ft_stripe.coupon cp 
    on (co.id = sd.coupon_id )
join ft_stripe.subscription_item si 
    on (si.subscription_id = s.id )
join ft_stripe.plan p 
    on (si.plan_id = p.id )
join ft_stripe.customer c 
    on (c.id = s.customer_id)
where (s.canceled_at is null or s.trial_start is NULL or s.canceled_at > s.trial_end)
 -- handle the case where the user canceled during the trial
 	and s.cancelled_at is not null