Hi, I’m doing the exercise “Identifying the most popular book in the catalogue”, I actually arrived to the same answers, altough with a little different query, I want to know if my result as pure lucky or is just another way to solve it. My query was:
SELECT
name,
COUNT(user_id) AS number_users
FROM books_users bu
INNER JOIN books b
ON bu.book_id = b.id
GROUP BY 1
ORDER BY 2 DESC, 1 ASC
An the solution was the following:
SELECT
name,
COUNT(DISTINCT(bu.user_id))
FROM books_users bu
INNER JOIN books b
ON bu.book_id = b.id
GROUP BY 1
ORDER BY 2 DESC, name ASC
Also, I want to know what’s the “general rule” to do inner joins, is there a difference which table I use first and which I join?
Thanks!!
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!!
Hi, so I learn better by knowing all the elements of the topic I’m learning, like “the big picture”, in this case I want to know the elements of a SQL query, I have the following:
I don’t know if I’m missing something, also I don’t know how the AND
and BETWEEN - AND
are catalogued
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’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!