Mobile Analytics

I am not sure why my query returned as error for exercise 201.

WITH ab_test_categorization AS (
  SELECT
    user_id,
    action,
    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 IN ('signup', 'add-library-entry')
)    

SELECT
  ab_test_variation,
  action,
  COUNT(DISTINCT(user_id)),
  100.0 * COUNT(DISTINCT(CASE WHEN ab_test_variation = 'control' AND action = 'add-library-entry' THEN user_id END)) / COUNT(DISTINCT(CASE WHEN ab_test_variation = 'control' AND action = 'signup' THEN user_id END))
FROM ab_test_categorization
WHERE
  ab_test_name = 'longer_onboarding_201803'
GROUP BY 1, 2

The bit at the final query

100.0 * COUNT(DISTINCT(CASE WHEN ab_test_variation = 'control' AND action = 'add-library-entry' THEN user_id END)) / COUNT(DISTINCT(CASE WHEN ab_test_variation = 'control' AND action = 'signup' THEN user_id END))

returned ERROR: division by zero.

I tried to further investigate myself by checking both the numerator and denominator part of the calculation independently and it works fine. I managed to answer the question by doing manually calculation from the table without the line of calculation that I got an error above.

Can you explain the error that I made above?

Many thanks, the exercises have been interesting and useful!

So I tried to recreate the results for Exercise 185 Activation rate per country but without using the mobile_analytics.events table.

The results can’t be replicated due to differences in tables i.e. number of unique users in mobile_analytics.events table: 2508 number of unique iphone users in devices table: 4223

But is the logic of my query attempt correct?

WITH mob_readers_and_users AS (
  SELECT 
    country,
    COUNT(DISTINCT CASE WHEN last_page > 0 THEN d.user_id END) AS reader_num, 
    COUNT(DISTINCT d.user_id) AS user_num
  FROM users u
  LEFT JOIN books_users b
    ON u.id = b.user_id
  LEFT JOIN devices d 
    ON u.id = d.user_id
  WHERE 
    device_type = 'iphone'
  GROUP BY 1
)

SELECT 
  country, 
  reader_num, 
  ROUND(100.0 * reader_num / user_num, 2) AS activation_rate
FROM mob_readers_and_users
ORDER BY 3 DESC