SQL: find customers who churned right after their second invoice

ChatGPT Chat anonymous·4d ago #sql #analytics

Postgres. Tables: `customers(id, created_at)`, `invoices(id, customer_id, paid_at, amount)`, `subscriptions(customer_id, cancelled_at)`. I want customers whose subscription was cancelled within 14 days after their second paid invoice. Also show the two invoice dates.

You

Postgres. Tables: customers(id, created_at), invoices(id, customer_id, paid_at, amount), subscriptions(customer_id, cancelled_at). I want customers whose subscription was cancelled within 14 days after their second paid invoice. Also show the two invoice dates.

ChatGPT

WITH ranked AS (
  SELECT customer_id,
         paid_at,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY paid_at) AS n
  FROM invoices
  WHERE paid_at IS NOT NULL
),
second AS (
  SELECT customer_id,
         MAX(paid_at) FILTER (WHERE n = 1) AS first_paid,
         MAX(paid_at) FILTER (WHERE n = 2) AS second_paid
  FROM ranked
  WHERE n <= 2
  GROUP BY customer_id
  HAVING COUNT(*) = 2
)
SELECT s.customer_id, s.first_paid, s.second_paid, sub.cancelled_at
FROM second s
JOIN subscriptions sub ON sub.customer_id = s.customer_id
WHERE sub.cancelled_at IS NOT NULL
  AND sub.cancelled_at >= s.second_paid
  AND sub.cancelled_at <  s.second_paid + INTERVAL '14 days'
ORDER BY sub.cancelled_at DESC;

Notes:

ROW_NUMBER gives each customer’s invoices a rank by date; the second CTE keeps only people with at least two paid invoices.
FILTER (WHERE …) is the tidy Postgres way to pivot the first and second dates into columns.
If a customer can have more than one subscription row, add DISTINCT ON (customer_id) or aggregate MIN(cancelled_at); otherwise you’ll double count.

You

Can I get the % of all two-invoice customers this represents?

ChatGPT

Wrap it:

SELECT COUNT(*) FILTER (WHERE churned) * 100.0 / COUNT(*) AS pct_churned_after_2nd
FROM (
  SELECT s.customer_id,
         EXISTS (
           SELECT 1 FROM subscriptions sub
           WHERE sub.customer_id = s.customer_id
             AND sub.cancelled_at >= s.second_paid
             AND sub.cancelled_at <  s.second_paid + INTERVAL '14 days'
         ) AS churned
  FROM second s
) t;

(Reuse the ranked and second CTEs from above.) The * 100.0 forces a decimal result; without it Postgres does integer division and you get 0.

Comments (0)

No comments yet.

Report this post