59. Сounting signups per country: intro to GROUP BY

While Bindle founders were dealing with partnerships with other companies and book stores Facebook campaigns got some traction. Users from DACH region and from English speaking countries started to sign up.

The only question was – how did the campaigns actually perform?

Were they profitable? Profitable means we earned more money than we spent. A marketer would say something like “Do our campaigns have positive ROI?”

What was the impact? How many users signed up from a certain country we targeted? How many signups and customers did we acquire day by day?

Which campaigns are good and should we keep them? Which campaigns are bad? We need to learn from these ones and shut them down.

In this chapter and next chapters we’ll learn in detail how to answer all these questions ☝ Now let’s start with segmenting our users by country.

Count users per country

We already know how to count signups in a specific country, US for example:

SELECT 
  COUNT(*)
FROM users
WHERE
  country = 'us'

If we want to count signups in 20 countries – we just need to run 20 queries. Sounds scary, right? 🙈

As usual, SQL has our back in this challenge as well. The query above ☝ uses aggregate function COUNT(*). Let’s briefly remember how it works. It calculates a value (COUNT calculates the number of records) for a group of records. The query above ☝ has a single group – all users with country “us”.

Same way we can have multiple groups to aggregate. It’s possible with GROUP BY clause:

SELECT
  country,
  COUNT(*) AS users_count
FROM users
GROUP BY country

If the query has GROUP BY clause the records will be first split into different groups. Each group then will be aggregated into a number (via an aggregate function).

⚠ Here’s some intuition to understand and remember how GROUP BY works. GROUP BY splits all records into groups (one group for each value inside country column). After we calculate an aggregated value for each group. This is why in our SELECT statement we should have a column we split our records by (in our case it’s country) and the result of the aggregate function.

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