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’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
Many thanks!
Hi Anatoli!
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