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
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.