27. Calculating purchase rate. Part 2

In the previous lesson we stopped at the query that counts both numbers of users and customers within the first week after Bindle was launched:

SELECT
  COUNT(*) AS total_users_count,
  COUNT(
    CASE WHEN status = 'customer' THEN id END
  ) AS total_customers_count
FROM users
WHERE
  signup_date BETWEEN '2018-01-01' AND '2018-01-07'

Since we want to calculate a purchase rate (percentage of users who became customers) we need to divide total_customers_count by total_users_count. Not manually of course, but in SQL. Turns out it’s as simple as on a calculator:

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

Turns out we can perform different operations on columns: mathematical (for example divide value in one column by the other), combine string fields (for example we can combine first_name and last_name into a new column full_name), logical (we already saw it with if/else logic via the CASE statement), etc. All this gives us so much power to answer questions with data! The only thing which is left is to ask the right questions 😉 Go and practice new queries 📈 🚀

Anatoli Makarevich, author of SQL Habit About SQL Habit

Hi, it’s Anatoli, the author of SQL Habit. 👋

SQL Habit is a course (or, as some of the students say, “business simulator”). It’s based on a story of a fictional startup called Bindle. You’ll play a role of their Data Analyst 📊 and solve real-life challenges from Business, Marketing, and Product Management.

SQL Habit course is made of bite-sized lessons (you’re looking at one atm) and exercises. They always have a real-life setting and detailed explanations. You can immediately apply everything you’ve learned at work. 🚀

“well worth the money”

Fluent in SQL in a month

Master Data Analysis with SQL with real life examples from Product Management, Marketing, Finance and more.
-- Type your query here, for example this one -- lists all records from users table: SELECT * FROM users
LIMIT 500
Loading chart... ⏳