Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Conversion rates
Time to first order
Conversion charts
15. 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 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.

Exercise

Show two metrics in two different columns:

  1. order_on_registration_date – the number of people who made their first order within one day from their registration date.
  2. order_after_registration_date – 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 first_order_date - registration_date < INTERVAL '1' day THEN customer_id END)