SQL Habit Forum

Welcome to SQL Habit Community Forum 👋 Ask anything about the SQL Habit platform, Data Analysis, applications of data for running Web or Mobile products, etc.

Getting an error

From Exercise 66:

Let’s practice our newly learned skill of grouping and counting. Which email domain has the most amount of users at Bindle?

I am writing a code other way, by forming function. I am getting error. Could anyone help me to identify where I have mistaken?

WITH domain AS (
  SELECT
    SPLIT_PART (email, '@', 2) AS email_domain,
    FROM users
)

SELECT
  email_domain,
  COUNT(*)
FROM domain
GROUP BY 1
ORDER BY 2 DESC

Lesson 26 part 1 and part 2

This might be a bit silly, I am new to SQL so pardon my simple queries. I was practicing calculating purchase rate in lesson 26 and I decided to create my own queries. The question my own query should answer is “What is the % of users who are trial users and signed up in the first 2 weeks of Bindle existance”.

So I wrote a query that calculates this (Query 1), and I wanted to check this with another query (Query 2) to see if the result is correct. I can’t figure out why I am missing 1% of the users when I run the 2nd query.

Query 1

Calcualate % of the users who are trial users and signed up in the first 2 weeks of Bindle existance. This query produces a result of 36%:

SELECT
  100 * COUNT(CASE WHEN status = 'trial' THEN id END) /
  COUNT(*) AS trial_users_first_2_weeks_Jan_2018
FROM users
WHERE
  signup_date BETWEEN '2018-01-01' AND '2018-01-14'  

Query 2

Below query should return 64% as the query above returned 36% but it returns 63% and I don’t know why.

SELECT
  100 * COUNT(CASE WHEN status = 'customer' OR status = 'free' THEN id END) /
  COUNT(*) AS non_trial_users_first_2_weeks_Jan_2018
FROM users
WHERE
  signup_date BETWEEN '2018-01-01' AND '2018-01-14'

Query 3

Below query shows all unique values for status column in the user table for the same time frame as previous queries. I used it to figure out how to structure second query.

SELECT DISTINCT status
FROM users
WHERE 
  signup_date BETWEEN '2018-01-01' AND '2018-01-14'

Thank you.