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 registration_date >= '2016-01-01'
AND registration_date < '2017-01-01'
THEN customer_id
END) AS registrations_2016,
COUNT(CASE
WHEN registration_date >= '2017-01-01'
AND registration_date < '2018-01-01'
THEN customer_id
END) AS registrations_2017
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 registrations_2016 column checks if the registration_date is in 2016. If it is, the customer_id 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:
order_on_registration_date – the number of people who made their first order within one day from their registration date.order_after_registration_date – the number of people who made their first order after their registration date.Calculate the first metric in the following way:
COUNT(CASE WHEN first_order_date - registration_date < INTERVAL '1' day THEN customer_id END)
