I’m trying to use the COUNT aggregate function after the THEN statement of a CASE but I always get the value seted on the ELSE. I tested the sub-query funnel and it’s working, the problem is in the CASE.
The code below:
WITH funnel AS(
SELECT
DISTINCT q.user_id,
CASE
WHEN h.user_id IS NOT NULL
THEN ‘True’
ELSE ‘False’
END AS ‘is_home_try_on’,
h.number_of_pairs,
CASE
WHEN p.user_id IS NOT NULL
THEN ‘True’
ELSE ‘False’
END AS ‘is_purchase’
FROM quiz AS q
LEFT JOIN home_try_on AS h
ON q.user_id = h.user_id
LEFT JOIN purchase AS p
ON p.user_id = h.user_id)
SELECT
CASE
WHEN number_of_pairs LIKE '3 pairs’
AND is_purchase LIKE 'True’
THEN COUNT(*) ELSE '0’
END AS ‘3 pairs’,
CASE
WHEN number_of_pairs LIKE ‘5 pairs’
AND is_purchase LIKE ‘True’
THEN COUNT(*) ELSE ‘0’
END AS ‘5 pairs’
FROM funnel;
I’m trying to complete the funnel of the Warby Parker project.