I don’t quite understand why this sub query (below) is turning up zeros, rather than purchase rate to question 87. I see from the answer I could have simplified this into one query, but I don’t understand why this way doesn’t work as well? Thanks for your help!
with tabletime as ( select utm_campaign, COUNT(*) AS userscount, Count(CASE WHEN status = 'customer' THEN id END) AS payingcustomers from users WHERE utm_campaign IS NOT NULL GROUP BY utm_campaign ) SELECT utm_campaign, 100 * (payingcustomers / userscount) as purchase_rate FROM tabletime
I tried solving 106 problem using the following query:
SELECT utm_campaign, SUM(amount) AS total_revenue, COUNT(DISTINCT a.id) AS total_users, SUM(amount)/COUNT(DISTINCT b.id) AS ARPU FROM purchases a JOIN users b ON a.user_id = b.id AND a.refunded = FALSE -- WHERE utm_campaign IS NOT NULL GROUP BY 1 ORDER BY 4 DESC
which should be quite similar with the proposed solution, the difference is on the order of FROM clause. I used purchase table before user table and it gives a very different results.
I’d like to understand why is this the case as this is a quite surprising SQL behavior to me.
I’m doing the “Biggest age group in USA” exercise, however, I was trying to experiment and to a little change in the analysis by trying to visualize the groups by country, but I keep getting an error with the following query:
SELECT CASE WHEN age < 13 THEN 'kid' WHEN age < 18 THEN 'teenager' WHEN age < 25 THEN 'college' WHEN age < 35 THEN 'young adult' WHEN age < 56 THEN 'middle age' ELSE 'older adult' END AS age_group, country, COUNT(*) FROM users GROUP BY 1 GROUP BY country
Thank you all!!
Hey! Why are we saying the below?
If we use refunded = FALSE filter in the WHERE clause (filtering out all users who don’t have purchases) we’d calculate ARPPU.
Don’t we still have all the free/trial customers that shouldn’t be in ARPPU but wouldn’t be filtered out if we only use refunded as a filter? Thanks!
I was trying to solve the “Most popular email domain” exercise, and I already completed it, however at first I was trying to do the following query, but it keeps getting me an error, don’t know where is failing, any clues?
WITH domains AS( SELECT SPLIT_PART(email, '@', 2) AS email_domain, COUNT(*) FROM users ) SELECT SPLIT_PART(email_domain, '.', 1) AS main_domain FROM domains
How to distinguish between direct visits, referral visits and clicks from organic search when tracking with a custom pixel? In the course, the main info on campaigns comes from UTMs from the URL, but you would also wanna analyze organic traffic, that does not usually have tracking parameters in a link.