probability

WITH Customers AS (
  (SELECT 1 AS Customer, 1 AS Tea, 1 AS Scones) UNION ALL 
  (SELECT 2 AS Customer, 0 AS Tea, 1 AS Scones) UNION ALL
  (SELECT 3 AS Customer, 0 AS Tea, 0 AS Scones) UNION ALL
  (SELECT 4 AS Customer, 0 AS Tea, 0 AS Scones) UNION ALL
  (SELECT 5 AS Customer, 1 AS Tea, 1 AS Scones) UNION ALL
  (SELECT 6 AS Customer, 1 AS Tea, 0 AS Scones) UNION ALL  
  (SELECT 7 AS Customer, 1 AS Tea, 0 AS Scones) UNION ALL 
  (SELECT 8 AS Customer, 1 AS Tea, 1 AS Scones) UNION ALL
  (SELECT 9 AS Customer, 1 AS Tea, 0 AS Scones) UNION ALL
  (SELECT 10 AS Customer, 1 AS Tea, 0 AS Scones) UNION ALL
  (SELECT 11 AS Customer, 0 AS Tea, 0 AS Scones) UNION ALL
  (SELECT 12 AS Customer, 1 AS Tea, 1 AS Scones) ORDER BY Customer ASC)
,CustomersTotal AS (SELECT COUNT(*) AS Total FROM Customers)
  
-- SELECT * FROM Customers
-- P(Tea), i.e. probability that a customer orders tea
-- SELECT SUM(IF(Tea=1, 1, 0)) / COUNT(Customers) FROM Customers
-- SELECT Selection.Total / Entire.Total  FROM (SELECT COUNT(*) AS Total FROM Customers) AS Entire, (SELECT COUNT(*) AS Total FROM Customers WHERE Tea = 1) AS Selection
-- SELECT Tea, Count / Total FROM CustomersTotal, (SELECT Tea, COUNT(Tea) AS Count FROM Customers GROUP BY Tea ORDER BY Tea ASC) AS Selection

-- P(Scones), i.e. probability that a customer orders a scone
-- SELECT SUM(IF(Scones=1, 1, 0)) / COUNT(Customers) FROM Customers

-- P(Tea & Scones), i.e. probabilty that a customer orders both
-- SELECT SUM(IF(Tea=1 AND Scones=1, 1, 0)) / COUNT(Customers) FROM Customers
-- SELECT Selection.Total / Entire.Total  FROM (SELECT COUNT(*) AS Total FROM Customers) AS Entire, (SELECT COUNT(*) AS Total FROM Customers WHERE Tea = 1 AND Scones = 1) AS Selection

-- P(Scones | Tea = 1), i.e. probability that the customer orders a scone if they ordered tea
-- SELECT COUNTIF(Scones = 1) / COUNT(*) AS Total FROM Customers WHERE Tea = 1
-- SELECT Selection.Total / Entire.Total  FROM (SELECT COUNT(*) AS Total FROM Customers WHERE Scones = 1) AS Entire, (SELECT COUNT(*) AS Total FROM Customers WHERE Tea = 1) AS Selection
-- SELECT Tea, Count / Total FROM CustomersTotal, (SELECT Tea, COUNT(Tea) AS Count FROM Customers GROUP BY Tea ORDER BY Tea ASC) AS Selection
-- SELECT Scones, COUNT(Scones) FROM Customers WHERE Tea = 1 GROUP BY Scones ORDER BY Scones ASC

-- ,TeaCustomers AS (SELECT * FROM Customers WHERE Tea = 1)
-- ,TeaCustomersTotal AS (SELECT COUNT(*) AS Total FROM TeaCustomers)
-- SELECT *, 
--   SconesTotal / TeaCustomersTotal AS ProbSconeGivenTea, 
--   TeaCustomersTotal / CustomersTotal AS ProbTea, 
--   (SconesTotal / TeaCustomersTotal) * (TeaCustomersTotal / CustomersTotal) AS ProbSconesAndTea 
-- FROM 
--   (SELECT Scones, COUNT(Scones) AS SconesTotal FROM TeaCustomers GROUP BY Scones ORDER BY Scones ASC), 
--   (SELECT COUNT(TeaCustomers) AS TeaCustomersTotal FROM TeaCustomers), 
--   (SELECT COUNT(Customers) AS CustomersTotal FROM Customers)

-- SELECT Scones, Tea
-- FROM (SELECT Scones, COUNT(Scones) AS SconesTotal FROM Customers GROUP BY Scones ORDER BY Scones ASC)
--     ,(SELECT Tea, COUNT(Tea) AS TeaTotal FROM Customers GROUP BY Tea ORDER BY TEA ASC)

SELECT 
  *, 
  ROUND(TeaTotal / CustomerTotal, 2) AS PrTea, 
  ROUND(SconesTotal / CustomerTotal, 2) AS PrScones, 
  ROUND(JointTotal / TeaTotal, 2) AS PrSconesGivenTea, 
  ROUND( ((JointTotal / SconesTotal)*(SconesTotal / CustomerTotal)) / (TeaTotal / CustomerTotal), 2) AS PrSconesGivenTeaBayes,
  ROUND(JointTotal / SconesTotal, 2) AS PrTeaGivenScones,
  ROUND( ((JointTotal / TeaTotal) * (TeaTotal / CustomerTotal)) / (SconesTotal / CustomerTotal), 2) AS PrTeaGivenSconesBayes
FROM 
  (SELECT Tea, Scones, COUNT(*) AS JointTotal FROM Customers GROUP BY Tea, Scones ORDER BY Tea, Scones ASC) AS Joint
  INNER JOIN (SELECT Scones, COUNT(Scones) AS SconesTotal FROM Customers GROUP BY Scones) USING(Scones)
  INNER JOIN (SELECT Tea, COUNT(Tea) AS TeaTotal FROM Customers GROUP BY Tea) USING(Tea),
  (SELECT COUNT(Customers) AS CustomerTotal FROM Customers)
ORDER BY Tea, Scones