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