Well done! The CASE WHEN construction can also be used to show multiple metrics in a single query. Take a look:
SELECT COUNT(CASE WHEN RegistrationDate >= '20160101' AND RegistrationDate < '20170101' THEN CustomerId END) AS Registrations2016, COUNT(CASE WHEN RegistrationDate >= '20170101' AND RegistrationDate < '20180101' THEN CustomerId END) AS Registrations2017 FROM Customers;
We use COUNT() with a CASE WHEN inside. The purpose is to count only the users in each column who match the given criteria. For instance, the Registrations2016 column checks if the RegistrationDate is in 2016. If it is, the CustomerId is counted. If the condition isn't satisfied – and there is no alternative condition or ELSE part – CASE WHEN returns NULL and the customer isn't counted.
COUNT(CASE WHEN...) is a technique used to include multiple metrics in different columns of the same report.
Show two metrics in two different columns:
OrderOnRegistrationDate – the number of people who made their first order on their registration date.OrderAfterRegistrationDate – the number of people who made their first order after their registration date.Calculate the first metric in the following way:
COUNT(CASE WHEN DATEDIFF(Day, RegistrationDate, FirstOrderDate) = 0 THEN CustomerId END)
