End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Conversion rates
Time to first order
11. Average time to first order in weekly cohorts
Conversion charts
Summary

Instruction

Very well done! Previously, we created a report showing conversion rates in weekly registration cohorts. In a similar way, we can create a report showing the average time from registration to first order in weekly registration cohorts. Take a look:

SELECT
  DATE_PART('year', registration_date) AS year,
  DATE_PART('week', registration_date) AS week,
  AVG(first_order_date - registration_date) AS avg_days_to_first_order
FROM 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);

Result:

year week avg_days_to_first_order
2016 28 0
2016 29 19
2016 30 14
... ... ...

The report looks very similar to the previous one. Once again, we used an interval to group and order the rows by weekly registration cohorts. The only difference is the avg_days_to_first_order column.

Exercise

Calculate the average number of days that passed between registration and first order in quarterly registration cohorts. Show the following columns: year, quarter, and avg_days_to_first_order. Order the results by year and quarter.

Stuck? Here's a hint!

You'll have to use:

DATE_PART('quarter', registration_date)