Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Customer Behavior Analysis in SQL

Introduction
Customer cohorts
Summary

Instruction

Let's start with customer acquisition. We'll begin by counting how many new registrations occurred in a given period. By observing how the registration count changes over time, we'll be able to assess whether our promotional campaigns have worked or not.

We'll discuss the following reports:

  • The number of registrations in the given period.
  • The number of registrations in the current period of time (current week, current month, etc.).
  • The number of registrations over time, tallying registrations in each month or in each week.

To count the number of registrations in 2016, we can use the following query:

SELECT COUNT(customer_id) AS registration_count_2016
FROM customers
WHERE registration_date >= '2016-01-01'
  AND registration_date <  '2017-01-01';

The query is quite simple. Just remember proper time filtering: use the 'yyyy-mm-dd' format (year, month, day) when you write the dates.

Exercise

How many customers registered in the first six months of 2017? Name the column registration_count.

Stuck? Here's a hint!

To get the first six months of 2017, use the following WHERE clause:

WHERE registration_date >= '2017-01-01'
  AND registration_date <  '2017-07-01';