Data analytics tasks

[Rick]

G'day

I would like to add some exercises that show how to use SQL for common analytics tasks. Thus, I have just added to the Classic models exercises <http://richardtwatson.com/dm6e/Reader/ClassicModels.html> the following query

A common retail analytics task is to analyze each basket or order to learn what products are often purchased together. Report the names of products that appear in the same order ten or more times.

The answer is available as part of the instructor's manual <http://richardtwatson.com/dm6e/private/ClassicModels.html>

SELECT CONCAT((SELECT productName FROM Products WHERE Products.productCode = OD1.productCode), ", ", (SELECT productName FROM Products WHERE Products.productCode = OD2.productCode)) 
AS Pair, count(*) AS Frequency 
FROM OrderDetails AS OD1 JOIN OrderDetails AS OD2 ON OD1.orderNumber = OD2.orderNumber 
WHERE OD1.productCode > OD2.productCode
GROUP BY Pair HAVING Frequency >=10
ORDER BY Frequency DESC, Pair;

Please let me know if there are other common analytics task I could show how to handle with SQL.

All the best for the next semester.

Cheers  

Rick