Autumn Offers - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Customer activity
Finding good customers
Summary
15. Recap

Instruction

Perfect! It's time to wrap things up.

  1. To find the number of customers active within the last 30 days in weekly registration cohorts, use:
    SELECT
      DATE_PART('year', registration_date) AS year,
      DATE_PART('week', registration_date) AS week,
      COUNT(*) AS active_customers
    FROM customers
    WHERE CURRENT_DATE - last_order_date < INTERVAL '30' day
    GROUP BY DATE_PART('year', registration_date), DATE_PART('week', registration_date)
    ORDER BY DATE_PART('year', registration_date), DATE_PART('week', registration_date);
    
  2. To find the average order value in weekly registration cohorts, use:
    SELECT
      DATE_PART('year', registration_date) AS year,
      DATE_PART('week', registration_date) AS week,
      AVG(total_amount) AS average_order_value
    FROM customers c
    JOIN orders o
      ON c.customer_id = o.customer_id
    GROUP BY DATE_PART('year', registration_date), DATE_PART('week', registration_date)
    ORDER BY DATE_PART('year', registration_date), DATE_PART('week', registration_date);
    
  3. To find the number of "good customers" in weekly registration cohorts, use:
    WITH good_customers AS (
    SELECT
      c.customer_id,
      registration_date,
      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
    HAVING AVG(total_amount) > 1636
    )
    SELECT
      DATE_PART('year', registration_date) AS year,
      DATE_PART('week', registration_date) AS week,
      COUNT(*) AS good_customers
    FROM good_customers
    GROUP BY DATE_PART('year', registration_date), DATE_PART('week', registration_date)
    ORDER BY DATE_PART('year', registration_date), DATE_PART('week', registration_date);
    

How about a quick quiz before we move on to the next part?

Exercise

Click Next exercise to continue.