-- 1. Number of distinct Customers by Product
SELECT FkProduct, COUNT(DISTINCT FkCustomer) AS NCustomer
FROM InvoiceLines, Invoices
WHERE FkInvoiceNo=PkInvoiceNo
GROUP BY FkProduct;

-- 2. Largest invoice revenue by Product
WITH TotalByInvoice AS
	(SELECT FkInvoiceNo As InvoiceNo, SUM(Qty*Price) As TotalInvoice
	FROM InvoiceLines
	GROUP BY FkInvoiceNo)
	SELECT FkProduct, MAX(TotalInvoice)
FROM InvoiceLines, TotalByInvoice
WHERE InvoiceNo=FkInvoiceNo
GROUP BY FkProduct;

-- 3. The percentage of revenue generated by the product over the total revenue of the customer by Customer and Product
WITH a AS
	(SELECT FkCustomer, FkProduct, SUM(Price) AS Revenue
	FROM InvoiceLines, Invoices
	WHERE FkInvoiceNo = PkInvoiceNo
	GROUP BY FkCustomer, FkProduct),
b AS
	(SELECT FkCustomer, SUM(Price) AS Revenue
	FROM InvoiceLines, Invoices
	WHERE FkInvoiceNo = PkInvoiceNo
	GROUP BY FkCustomer)
SELECT a.FkCustomer, a.FkProduct, 100.0*a.Revenue/b.Revenue AS Ratio
FROM a, b
WHERE a.FkCustomer = b.FkCustomer;

-- 4. Total revenue by Customer but only if Customer has bought FkProduct=7 at least once  
WITH a AS
	(SELECT FkCustomer, SUM(Price) As TotalRevenue
	FROM InvoiceLines, Invoices
	WHERE FkInvoiceNo = PkInvoiceNo
	GROUP BY FkCustomer),
b AS
	(SELECT DISTINCT FkCustomer
	FROM InvoiceLines, Invoices
	WHERE FkInvoiceNo = PkInvoiceNo AND FkProduct=7)
SELECT a.FkCustomer, a.TotalRevenue
FROM a, b
WHERE a.FkCustomer=b.FkCustomer;

-- 4. Simpler solution (JRS does not have CASE unfortunately)
SELECT FkCustomer, SUM(Price) As TotalRevenue
FROM InvoiceLines, Invoices
WHERE FkInvoiceNo = PkInvoiceNo
GROUP BY FkCustomer
HAVING SUM(CASE WHEN FkProduct=7 THEN 1 ELSE 0 END) > 0;
