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 Server

Final Quiz
3. Question 2

Instruction

Nice work! Let's move on to customer conversion.

Exercise

Create a conversion chart showing the 2017 Q1 monthly registration cohorts and the number of conversions (i.e., the number of customers who made their first purchase) in the first week, the first two weeks, and more than two weeks after registering. Show the following columns:

  • Month – the month of registration.
  • NoSale – the number of customers who never placed an order.
  • FirstWeek – the number of customers who placed their first order within the first week.
  • SecondWeek – the number of customers who placed their first order within the second week.
  • AfterSecondWeek – the number of customers who placed their first order after more than two weeks.

Order the results by month.

Stuck? Here's a hint!

Use the FirstOrderDate column to create this report.

You can find the number of customers who made a purchase within the first week with:

COUNT(CASE WHEN DATEDIFF(Day, RegistrationDate, FirstOrderDate) < 7 THEN CustomerID END) AS FirstWeek

Use this pattern in the other two similar columns.