SQL Habit Forum

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:

• Statements: select, from, order by
• Operators: as, or, in
• functions (here we have scalar and aggregate): count, sum, min, cast

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!

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!