Best April deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Conversion rates
Time to first order
Conversion charts
14. Multiple metrics in one query
Summary

Instruction

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.

Exercise

Show two metrics in two different columns:

  1. OrderOnRegistrationDate – the number of people who made their first order on their registration date.
  2. OrderAfterRegistrationDate – the number of people who made their first order after their registration date.

Stuck? Here's a hint!

Calculate the first metric in the following way:

COUNT(CASE WHEN DATEDIFF(Day, RegistrationDate, FirstOrderDate) = 0 THEN CustomerId END)