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 customer_id, registration_date, channel_id
ORDER BY 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.