Great! Now we'd like to calculate the global average time to first order. This is a good indicator of how long a typical customer takes to make the first purchase. Take a look:
SELECT AVG(1.0 * DATEDIFF(Day, RegistrationDate, FirstOrderDate)) AS AvgDaysToFirstOrder FROM Customers;
All we had to do was use AVG() with DATEDIFF() inside. Here we calculated the average number of days, but we can also use other time units.
Note that the DATEDIFF() function returns integers, and in SQL Server the AVG() function with integer arguments will return an integer. To get more precise results, you'll need to multiply the AVG()'s arguments by 1.0 – that way the function will use floating point numbers, and it will return one.
Find the average time to first order for each channel. Show two columns: ChannelName and AvgDaysToFirstOrder.
Join the Customers and Channels tables to get channel names. Group by channel IDs and channel names.
