Well done! The
CASE WHEN construction can also be used to show multiple metrics in a single query. Take a look:
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
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.