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

Instruction

Good! Now let's dig into some customer activity analysis. The number of active customers is an important metric in any business, especially in businesses offering their products or services online. Sometimes it's easy to say who is an active customer: for a subscription service someone with an active subscription is an active user, for a mobile app someone who has the app installed and uses it once a week is an active user, etc.

For an e-store the definition of an "active customer" is not as straightforward. There are some customers who place orders every now and then, but there are also some who haven't been active for a long time. In our online supermarket, regular customers typically place one order a week, but we'll define "active customers" as all customers who've placed an order within the last 30 days. Let's count how many active customers we have:

SELECT COUNT(*) AS active_customers
FROM customers
WHERE CURRENT_DATE - last_order_date < INTERVAL '30' day;


In the WHERE clause, we created an interval: we subtracted last_order_date from CURRENT_DATE. This lets us check if the given customer's most recent order was within the last 30 days (by comparing the difference to the INTERVAL '30' day).

Exercise

Find the number of active customers in each country. Show two columns: country and active_customers (number of those who have placed an order within the last 30 days). Do you see any major differences between countries?