I’m trying the following query:
WITH domains AS ( SELECT SPLIT_PART(email, '@', 2) AS email_domain, COUNT(*) AS recount FROM users GROUP BY email_domain ) SELECT DISTINCT(SPLIT_PART(email_domain, '.', 1)) AS maindomain, recount FROM domains GROUP BY maindomain, recount ORDER BY recount DESC
But I want to group the domain before the dot as one, for example I want all the “boogle’s” to count as one domain and sum the total of users with that domain (idependently of their country), but it keeps counting them as if they were different ids or domains.
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
Hi:) Why does the following syntax not work and is there a way to write it without CTEs?
SELECT COUNT(DISTINCT(user_id)), custom_parameters ->> 'ab_test_name' AS ab_test_name, custom_parameters ->> 'ab_test_variation' AS ab_test_variation, created_at AS categorized_at FROM mobile_analytics.events WHERE custom_parameters ->> 'ab_test_name' IS NOT NULL AND action = 'signup' AND ab_test_name = 'longer_onboarding_201803' GROUP BY ab_test_variation
Hello, I’m struggling to understand the formula used in the exercise to calculate the churn rate:
SELECT 100 - 100 * COUNT(DISTINCT(b.visitor_id)) / COUNT(DISTINCT(h.visitor_id))::float AS churn_rate FROM web_analytics.pageviews h
I don’t get the 100-….why cannot we leave it as:
100 * COUNT(DISTINCT(b.visitor_id)) / COUNT(DISTINCT(h.visitor_id))::float
I have a question about the funnel analysis query in lesson 129, namely this one:
SELECT COUNT(h.visitor_id) AS homepage_pvs, COUNT(b.visitor_id) AS book_page_pvs FROM web_analytics.pageviews h LEFT JOIN web_analytics.pageviews b ON h.visitor_id = b.visitor_id AND b.url LIKE '%/books/%' AND ( b.referer_url = 'https://www.bindle.com/' OR b.referer_url LIKE 'https://www.bindle.com/?%' ) AND b.created_at BETWEEN h.created_at AND h.created_at + '30 minutes'::interval WHERE h.url = 'https://www.bindle.com/' OR h.url LIKE 'https://www.bindle.com/?%'
My question is: Is the
AND ( b.referer_url = 'https://www.bindle.com/' OR b.referer_url LIKE 'https://www.bindle.com/?%' )
bit necessary, given that we filter using
WHERE further down? Is there a difference? They seem to give the same results.
In this code where we setup to capture retention, i know we join the table to itself and specify that the left side table has to be a signup action but we never specify what kind of action the right side (joined table) has to be.. doesnt this mean that technically we could be joining a signup action to itself? If thats the case, shouldnt we specify that the joined table’s action can’t be a signup one?
WITH user_activity AS ( SELECT u.user_id, u.created_at::date AS signup_date, e.created_at::date AS activity_date, COUNT(*) AS events_counts FROM mobile_analytics.events u LEFT JOIN mobile_analytics.events e ON e.user_id = u.user_id WHERE u.action = 'signup' GROUP BY 1, 2, 3 ORDER BY signup_date ASC, user_id ASC ) SELECT 100.0 * COUNT(DISTINCT(CASE WHEN activity_date = '2018-02-08' THEN user_id END)) / COUNT(DISTINCT(user_id)) AS D7_retention_rate FROM user_activity WHERE signup_date = '2018-02-01'
Hi, im curious to know if this solves the same problem? The answer is correct but i want to make sure my SQL is correct as well in terms of what we’re looking at.
with joined as ( SELECT a.creative_name, a.label, a.activity_kind, a.adid, b.event_name FROM adjust.callbacks a LEFT JOIN adjust.callbacks b ON a.adid = b.adid and b.event_name = 'signup' WHERE a.tracker = 'gxel3d1' AND a.activity_kind = 'click' ), numbers as ( SELECT creative_name, count(distinct(case when event_name = 'signup' then adid end)) as signups, count(distinct(adid)) as total FROM joined GROUP BY 1 ) SELECT *, 100.0*signups/total from numbers order by 2 desc
In the following code from Lesson 109.. how come we dont need to group by utm_campaign at the end when we are calculating CPA? is it because we joined the users to the row by utm_campaign?
WITH spend_per_campaign AS ( SELECT utm_campaign, SUM(amount) AS total_spend FROM marketing_spends GROUP BY 1 ), users_per_campaign AS ( SELECT utm_campaign, COUNT(*) AS users_count FROM users WHERE utm_campaign IS NOT NULL GROUP BY 1 ) SELECT s.utm_campaign, users_count, total_spend / users_count AS CPA FROM spend_per_campaign s INNER JOIN users_per_campaign u ON s.utm_campaign= u.utm_campaign
Does this look correct for the homework at the bottom of lesson 108 where it asks to add country to the CTE?
I chose inner join instead of left join but i dont think it would matter if i did left join?
WITH customers AS ( SELECT a.user_id, b.country, MIN(a.created_at) AS first_purchased_at FROM purchases a inner join users b on a.user_id = b.id WHERE refunded = FALSE GROUP BY 1,2 ORDER BY 3 DESC ) SELECT * FROM customers