End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Customer activity
Finding good customers
Summary

Instruction

Well done! We can also introduce more features into the previous report. We'll use these features to analyze if good customers have anything in common (other than high average order values). Check it out:

WITH good_customers AS (
  SELECT
    c.customer_id,
    registration_date,
    c.channel_id,
    AVG(total_amount) AS avg_order_value
  FROM customers c
  JOIN orders o
    ON c.customer_id = o.customer_id
  GROUP BY c.customer_id, registration_date, c.channel_id
  HAVING AVG(total_amount) > 1636
)

SELECT
  DATE_PART('year', registration_date) AS year,
  DATE_PART('week', registration_date) AS week,
  channel_id,
  COUNT(*) AS good_customers
FROM good_customers
GROUP BY c.customer_id, registration_date, c.channel_id
ORDER BY c.customer_id, registration_date;

In the query above, we've added a new customer field in the CTE: the acquisition channel. We used the channel_id column from the inner query and showed it in the outer query to get additional information about customers.

This way we can check if any channels are particularly good at attracting good customers. If we identify good channels, we can put more marketing efforts into these channels to attract even more good customers.

Exercise

We want to find out if there are any countries that are particularly good. The template code contains the query from the previous exercise. It shows the columns year, quarter, and good_customers. Extend the report so that it contains four columns: year, quarter, country, and good_customers. Order the results by year and quarter.

Stuck? Here's a hint!

You will first need to add the country column in the CTE before you can use it in the outer query.