Counting number of customers per country in a month

Hello everyone, I am in exercise 30: counting number of customers per country in a month.

The solution to the exercise has this query:

SELECT
  COUNT(*)
FROM users
WHERE 
  signup_date BETWEEN '2018-01-01' AND '2018-01-31'
  AND status = 'customer'
  AND country = 'in'

but I performed a different query:

SELECT
  COUNT(CASE WHEN status ='customer' THEN id END) AS count_customer_in      
FROM users
WHERE country = 'in'
      AND
      signup_date BETWEEN '2018-01-01' AND '2018-01-31';

that led me to the result.

My question is: Would it be good practice to query it in this last way? Regards.

REPLIES

makaroni4

4 months Reply

Hey @adolfo

Great question! I’m glad I can share a couple of advice early in your course journey.

You have the right solution and for the purpose of getting one number, it’d work perfectly. However, going forward I’d recommend 2 things: good formatting and keeping business logic in one place.

Formatting

You have a neat tidy query. Going forward, continue keeping SQL keyword casing (lower or UPPER) the same throughout the query, so your brain won’t waste extra energy parsing through SQL syntax. The same goes for the indentation level.

It’s OK to quickly put together a “dirty” Ad Hoc query, but on hundreds of queries you’d waste a lot of energy. Here’s an anti-pattern example:

select count(CASE when status ='customer' THEN id end) AS count_customer_in      
FROM users
where country = 'in'
  AND signup_date BETWEEN '2018-01-01' and '2018-01-31';

It’s quite painful to read it, isn’t it?

Business logic

Again, your query is great for this specific exercise. Although I’d put all filters together to show the intent and simplify a reader’s job to understand your query. That’s why in the solution all filters are combined in the WHERE clause:

SELECT
  COUNT(*)
FROM users
WHERE 
  signup_date BETWEEN '2018-01-01' AND '2018-01-31'
  AND status = 'customer'
  AND country = 'in'

You’d use if/else logic when you want to count all users and customers in the same query:

SELECT
  COUNT(*) AS all_users,
  COUNT(CASE WHEN status = 'customer' THEN id END) AS customers
FROM users
WHERE 
  signup_date BETWEEN '2018-01-01' AND '2018-01-31'  
  AND country = 'in'

As you can see, this exercise is too small for both of these best-practices to reveal themselves. I hope you’ve learned something new and will see how these best-practices shine down the road.

adolfo

4 months Reply

Thank you for your help and recommendations on format and business logic, this learning journey is amazing!

Counting number of customers per country in a month

REPLIES

Formatting

Business logic

WRITE A REPLY