SQL Habit Forum

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!

Hi, can you show an example of how to calculate Retention (1,7,30 days) by monthly cohorts? I have been trying to do it for 3 days on different datasets, the result is not working. thanks

I’m thinking about a list of Mac/Win shortcuts for actions like comment/uncomment, move lines, etc.

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