<p> The chart below shows the best selling products ranked according to the revenues they generate. Only the top 10 best selling products are shown.</p> <?php // Total Revenue by product $query = "SELECT ProductName, Revenue FROM ecommerce.ProductsVsCustomers_Pivot ORDER BY Revenue DESC limit 10"; $title = "Products by revenues"; query_and_print_graph($query, $title, "Euros"); ?> <p>The chart below shows the results of a similar analysis, this time to rank the customers that contribute the most to total revenues. Only the top 20 customers are shown below.</p> <?php // Page body. Write here your queries $query = "SELECT b.CustomerID Customer, sum(a.Quantity*a.UnitPrice) Revenues from ecommerce.order_details a left join ecommerce.orders b on a.OrderID=b.OrderID group by CustomerID order by Revenues desc limit 20"; $title = "Customers by revenues"; query_and_print_graph($query, $title, "Euros"); ?> <p>Once we have identified the best selling products and the top customers, we seek to improve our understanding of the relationships between them.</p> <p> We start from considering associations between product categories as observed in past transactions. Specifically, the chart below shown the links between pairs of categories according to the number of times they are bought together. The thicker the network edge connecting two categories, the more often those two categories are found together in the customers' baskets. The size of the circles is proportional to the total revenues that each product categories generates.</p> <center><img src="categories_network.png" style="width: 40%"></center> <p>The information provided in the network graph above could be used to informed marketing campaigns that cover two or more product categories, so that the marketing team could deploy offers for products that belong to categories that "go together". <p> We then go one layer further to look at the associations between products. The following table shows a ranking of pairs of products that tend to be purchased together. The pairs of products are ranked according to the number of times each pair appears in a transaction. To focus on the most relevant information, we show only the product pairs that appear at least five times. While this information does not, on its own, provide a fully-fledge recommendation system, it can provide insight on customers behaviour that can be used in setting up marketing campaigns.</p> <?php // Most sold product pairs $query = "SELECT\n\t\t\t P1.ProductName as Product_1,\n \t\t P2.ProductName as Product_2,\n \t\t Count(DISTINCT O1.OrderID) as Number_of_occurrences\n\t\t\t FROM ecommerce.products P1\n \t\t JOIN ecommerce.products P2\n \t ON P1.ProductID != P2.ProductID\n \t\t LEFT JOIN ecommerce.order_details O1\n INNER JOIN ecommerce.order_details O2\n ON O1.OrderID = O2.OrderID\n \t\tON O1.ProductID = P1.ProductId\n \tAND O2.ProductID = P2.ProductID \n\t\t\t WHERE P1.ProductID > P2.ProductID\n GROUP BY P1.ProductID, P2.ProductID\n HAVING COUNT(DISTINCT O1.OrderID)>=5\n\t\t\t ORDER BY Count(DISTINCT O1.OrderID) DESC";
<?php // Teams, winned games $query = "SELECT t.TeamName, avg(m.LeaguePoints)\n FROM Project.MatchStat AS m, Project.Teams AS t \n WHERE m.TeamID = t.TeamID\n GROUP BY m.teamID ORDER BY avg(m.LeaguePoints) DESC LIMIT 10"; $title = "Top Best Teams"; query_and_print_graph($query, $title, "Average League Points"); ?> <p>The chart below shows the results of a similar analysis, this time with the 10 worst teams of the history.</p> <?php // Page body. Write here your queries $query = "SELECT t.TeamName, avg(m.LeaguePoints)\n FROM Project.MatchStat AS m, Project.Teams AS t \n WHERE m.TeamID = t.TeamID \n GROUP BY m.teamID ORDER BY avg(m.LeaguePoints) ASC LIMIT 10"; $title = "Top Worst Teams"; query_and_print_graph($query, $title, "Average League Points"); ?> <p>Once we have seen the difference between best/worst teams, we are going to analyse the difference between teams playing home and away. It is interesting to notice that teams playing home in average win more and teams playing away commit more fouls.</p> <?php // Page body. Write here your queries $query = "SELECT 'FullTimeGoals' descrip, FullTimeGoals value\n from (SELECT avg(ms.FullTimeGoals) AS FullTimeGoals FROM Project.MatchStat AS ms WHERE ms.HomeAway = 'H') AS temp\n union all\n select 'LeaguePoints' descrip, LeaguePoints value\n from (SELECT avg(ms.LeaguePoints) AS LeaguePoints FROM Project.MatchStat AS ms WHERE ms.HomeAway = 'H') AS temp\n union all\n select 'HalfTimeGoals' descrip, HalfTimeGoals value\n from (SELECT avg(ms.HalfTimeGoals) AS HalfTimeGoals FROM Project.MatchStat AS ms WHERE ms.HomeAway = 'H') AS temp\n union all\n select 'Shots' descrip, Shots value \n from (SELECT avg(ms.Shots) AS Shots FROM Project.MatchStat AS ms WHERE ms.HomeAway = 'H') AS temp\n union all\n select 'ShotsOnTarget' descrip, ShotsOnTarget value\n from (SELECT avg(ms.ShotsOnTarget) AS ShotsOnTarget FROM Project.MatchStat AS ms WHERE ms.HomeAway = 'H') AS temp\n union all\n select 'Fouls' descrip, Fouls value\n from (SELECT avg(ms.FoulsCommitted) AS Fouls FROM Project.MatchStat AS ms WHERE ms.HomeAway = 'H') AS temp\n union all\n select 'YellowCards' descrip, YellowCards value \n from (SELECT avg(ms.YellowCards) AS YellowCards FROM Project.MatchStat AS ms WHERE ms.HomeAway = 'H') AS temp\n union all\n select 'RedCards' descrip, RedCards value\n from (SELECT avg(ms.RedCards) AS RedCards FROM Project.MatchStat AS ms WHERE ms.HomeAway = 'H') AS temp"; $query2 = "SELECT 'FullTimeGoals' descrip, FullTimeGoals value\n from (SELECT avg(ms.FullTimeGoals) AS FullTimeGoals FROM Project.MatchStat AS ms WHERE ms.HomeAway = 'A') AS temp\n union all\n select 'LeaguePoints' descrip, LeaguePoints value\n from (SELECT avg(ms.LeaguePoints) AS LeaguePoints FROM Project.MatchStat AS ms WHERE ms.HomeAway = 'A') AS temp\n union all\n select 'HalfTimeGoals' descrip, HalfTimeGoals value\n from (SELECT avg(ms.HalfTimeGoals) AS HalfTimeGoals FROM Project.MatchStat AS ms WHERE ms.HomeAway = 'A') AS temp\n union all\n select 'Shots' descrip, Shots value \n from (SELECT avg(ms.Shots) AS Shots FROM Project.MatchStat AS ms WHERE ms.HomeAway = 'A') AS temp\n union all\n select 'ShotsOnTarget' descrip, ShotsOnTarget value\n from (SELECT avg(ms.ShotsOnTarget) AS ShotsOnTarget FROM Project.MatchStat AS ms WHERE ms.HomeAway = 'A') AS temp\n union all\n select 'Fouls' descrip, Fouls value\n from (SELECT avg(ms.FoulsCommitted) AS Fouls FROM Project.MatchStat AS ms WHERE ms.HomeAway = 'A') AS temp\n union all\n select 'YellowCards' descrip, YellowCards value \n from (SELECT avg(ms.YellowCards) AS YellowCards FROM Project.MatchStat AS ms WHERE ms.HomeAway = 'A') AS temp\n union all\n select 'RedCards' descrip, RedCards value\n from (SELECT avg(ms.RedCards) AS RedCards FROM Project.MatchStat AS ms WHERE ms.HomeAway = 'A') AS temp"; $title = "Match Statistics"; query_and_print_multiple_graph($query, $query2, $title, "Average Number of Units"); ?> <p> In order to see if all the betting companies perform similary, we are interested in observing their performance.</p> <p> The chart below shows the percentage of prediction succes of each betting company.</p>
