Codecademy export

project.sqlite

--SELECT * from survey limit 10 ;
--select count(distinct user_id) from survey group by question ;
---select count(distinct user_id) * 100/count(*) as percentages from survey group by question 
--select * from quiz limit 5;
 --select * from home_try_on limit 5;
 --select * from purchase limit 5; 
 --SELECT DISTINCT q.user_id,
   --h.user_id IS NOT NULL AS 'is_home_try_on',
   --h.number_of_pairs,
   --p.user_id IS NOT NULL AS 'is_purchase'
---FROM quiz q
---LEFT JOIN home_try_on h
   --ON q.user_id = h.user_id
--LEFT JOIN purchase p
  -- ON p.user_id = q.user_id
--LIMIT 10; 







with base_table as ( select distinct q.user_id, h.user_id is not null as 'is_home_try_on', h.number_of_pairs as 'AB_variant',p.user_id is not null as 'is_purchase' from quiz q
left join have_try_on h on q.user_id = h.user_id left join purchase p
on q.user_id =p.user_id)
select AB_variant, sum(case when is_have_try_on=1
then 1
else 0
end) 'home_trial',
sum(case when is_purchase =1 
then 1
else 0
end)'purchase'
from base_table group by AB_variant having home_trial>0;