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
WHERE CURRENT_DATE - last_order_date < INTERVAL '30' day;
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).