I want to make chart like this.
But it's not working.
This is the code:
SELECT TO_CHAR(created, 'YYYY-MM') AS year_month, user_id
FROM futterpedia_compositeorder
Output:
year_month
user_id
2023-08
421,650
2023-10
416,788
2023-11
442,012
2023-07
109,132
2023-10
123,681
2023-11
445,513
2023-10
435,027
2023-07
109,132
Now I've query for retention. But it not working.
WITH user_counts AS (
SELECT TO_CHAR(created, 'YYYY-MM') AS year_month,
COUNT(DISTINCT user_id) AS user_count
FROM futterpedia_compositeorder
GROUP BY TO_CHAR(created, 'YYYY-MM')
),
retention_data AS (
SELECT TO_CHAR(t1.created, 'YYYY-MM') AS purchase_month,
TO_CHAR(t2.created, 'YYYY-MM') AS retention_month,
COUNT(DISTINCT CASE WHEN TO_CHAR(t2.created, 'YYYY-MM') = TO_CHAR(t1.created + INTERVAL '1 MONTH', 'YYYY-MM') THEN t1.user_id END) AS retained_users
FROM futterpedia_compositeorder t1
JOIN futterpedia_compositeorder t2
ON TO_CHAR(t2.created, 'YYYY-MM') >= TO_CHAR(t1.created, 'YYYY-MM')
AND TO_CHAR(t2.created, 'YYYY-MM') <= TO_CHAR(t1.created + INTERVAL '5 MONTH', 'YYYY-MM')
GROUP BY TO_CHAR(t1.created, 'YYYY-MM'), TO_CHAR(t2.created, 'YYYY-MM')
)
SELECT rd.purchase_month,
rd.retention_month,
CASE
WHEN uc.user_count = 0 THEN 0
ELSE ROUND((rd.retained_users * 100.0 / uc.user_count), 2)
END AS retention_percentage
FROM retention_data rd
JOIN user_counts uc ON rd.purchase_month = uc.year_month
ORDER BY rd.purchase_month, rd.retention_month;