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:

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.