Below is the solution but I want to know other ways to accomplish the same results (preferably in PostgreSQL). This is the DB

Question - How many customers have churned straight after their initial free trial? what percentage is this rounded to the nearest whole number?

WITH ranking AS (
SELECT
s.customer_id,
s.plan_id,
p.plan_name,
ROW_NUMBER() OVER (
PARTITION BY s.customer_id
ORDER BY s.plan_id) AS plan_rank
FROM dbo.subscriptions s
JOIN dbo.plans p
ON s.plan_id = p.plan_id)

SELECT
COUNT(*) AS churn_count,
ROUND(100 * COUNT(*) / (
SELECT COUNT(DISTINCT customer_id)
FROM dbo.subscriptions),0) AS churn_percentage
FROM ranking
WHERE plan_id = 4 -- Filter to churn plan
AND plan_rank = 2

CodePudding user response：

You can achieve the same results with a single aggregation on customer_id with a few CASE WHEN statements:

SELECT count(*) as total_customers
    ,count(case when total_subscriptions = 2
          and includes_free = 1
          and includes_churn = 1 then 1 end) as churn_count
    ,100 * count(case when total_subscriptions = 2
          and includes_free = 1
          and includes_churn = 1 then 1 end) / count(*) as target_percent 
FROM (
  SELECT customer_id
      ,count(*) as total_subscriptions
      ,max(case when plan_id = 0 then 1 else 0 end) as includes_free
      ,max(case when plan_id = 4 then 1 else 0 end) as includes_churn  
  FROM dbo.subscriptions
  GROUP BY customer_id
) AS tbl
-- Remove any records for people who didnt use the free trial
-- or people who are still on the free trial
WHERE includes_free = 1 AND total_subscriptions > 1

The difference between our solutions are:

1. Yours doesn't specify that the customer actually had a free trial
2. Mine doesn't include customers who went from Free -> Churn -> (something else)

Depending on your requirements you might want to make further alterations/use a different approach.