End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Conversion rates
Time to first order
Conversion charts
14. Custom classifications
Summary

Instruction

Excellent! To create the last report type in this part, we'll first need to learn how to create our own classifications.

Suppose we want to show each customer along with a custom category label. The label will indicate how long they took to make their first order.

SELECT
  customer_id,
  registration_date,
  first_order_date,
  CASE
    WHEN first_order_date IS NULL THEN 'no order'
    WHEN first_order_date - registration_date <= INTERVAL '7'  day THEN '1 week'
    WHEN first_order_date - registration_date <= INTERVAL '14' day THEN '2 weeks'
    ELSE 'more than 2 weeks'
  END AS time_to_first_order
FROM customers;

Result:

customer_id registration_date first_order_date time_to_first_order
1 2017-08-25 2017-08-25 1 week
8 2016-09-30 2016-10-10 2 weeks
22 2017-01-01 NULL no order
... ... ... ...

The new part in this query is the last column named time_to_first_order. It uses an SQL concept known as CASE WHEN. It checks the expressions introduced after each WHEN keyword. When it finds the first true expression, the corresponding value in the THEN part is assigned to the specified field.

In our query, we first check if the first_order_date column is NULL. If it is, we set the time_to_first_order value to 'no order'. If it isn't, we check if the period between registration and first purchase is equal to or less than 7 days. If it is, we set the time_to_first_order value to '1 week'. If it's not, we check the same period for equal to or less than 14 days (the value '2 weeks'). If this expression doesn't match either, we use the default value from the ELSE clause ('more than 2 weeks'). Note that each CASE WHEN expression must end with the word END.

Exercise

Our e-store has used three versions of the registration form:

    s
  1. 'ver1' – introduced when the e-store started.
  2. 'ver2' – introduced on Mar 14, 2017.
  3. 'ver3' – introduced on Jan 1, 2018.

For each customer, select the customer_id, registration_date, and the form version the user filled in at the time of registration. Name this third column registration_form.

Stuck? Here's a hint!

Use the following construction:

CASE
  WHEN registration_date < '2017-03-14' THEN 'ver1'
  WHEN registration_date < '2018-01-01' THEN 'ver2'
  ELSE 'ver3'
END