Synced via Snip

gistfile1.txt

# UniqueCustomerTrialPurchaseByMonth

SELECT DATE_TRUNC('MONTH', created) as month,
       COUNT(DISTINCT customer_id) as customer_purchased_trial
FROM charges
WHERE amount = 3900 AND paid = true AND status = 'succeeded' and refunded = false
AND created BETWEEN TIMESTAMP '2023-03-01' AND CURRENT_DATE
GROUP BY 1
ORDER BY 1;




WITH FirstCharges AS (
    SELECT 
       s.customer_id,
        MIN(c.created) as first_charge_date
    FROM subscriptions s
    JOIN charges c ON s.customer_id = c.customer_id
    WHERE c.amount = 7800 AND c.refunded = false and c.paid and c.status = 'succeeded'
    AND s.status != 'trialing'
    GROUP BY s.customer_id
)

SELECT 
    DATE_TRUNC('MONTH', first_charge_date) as month,
    COUNT(*) as trial_to_sub_conversions
FROM FirstCharges, customers c
WHERE c.id = FirstCharges.customer_id and c.created BETWEEN TIMESTAMP '2023-03-01' AND CURRENT_DATE
GROUP BY 1
ORDER BY 1