-- 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;