Output

Solution

To identify the invoice with the highest total price, and in the event of a tie, select the one with the smallest invoice_id, we can follow a systematic approach using SQL's aggregation and window functions. The solution involves calculating the total price for each invoice, determining the highest total price, handling ties by selecting the smallest invoice_id, and finally retrieving the detailed information of the selected invoice.

Approach Overview

1. Join Purchases with Products to Calculate Subtotal per Product:

   • Combine the Purchases and Products tables to access the price of each product.
   • Calculate the subtotal for each product in an invoice by multiplying price by quantity.

2. Aggregate Subtotals to Determine Total Price per Invoice:

   • Sum the subtotals for each invoice_id to obtain the total price of each invoice.

3. Identify the Invoice with the Highest Total Price:

   • Determine the maximum total price across all invoices.
   • In cases where multiple invoices share this maximum total price, select the one with the smallest invoice_id.

4. Retrieve Detailed Information of the Selected Invoice:

   • Fetch the product_id, quantity, and subtotal (price) for each product within the selected invoice.

SQL Query

WITH details_by_invoices AS
(
    SELECT     
        pu.invoice_id,
        pu.product_id,
        pu.quantity,
        pu.quantity * pr.price AS price
    FROM       
        Purchases AS pu
    INNER JOIN 
        Products AS pr
    ON         
        pu.product_id = pr.product_id 
), 
invoice_with_max_total AS
(
    SELECT   
        invoice_id
    FROM     
        details_by_invoices
    GROUP BY 
        invoice_id
    ORDER BY 
        SUM(price) DESC,
        invoice_id ASC 
    LIMIT 1
)
SELECT 
    product_id,
    quantity,
    price
FROM   
    details_by_invoices
WHERE  
    invoice_id IN
    (
        SELECT invoice_id
        FROM invoice_with_max_total
    );

Step-by-Step Approach

Step 1: Join Purchases with Products and Calculate Subtotal per Product (details_by_invoices)

Combine the Purchases and Products tables to calculate the subtotal for each product within an invoice.

SQL Snippet:

WITH details_by_invoices AS
(
    SELECT     
        pu.invoice_id,
        pu.product_id,
        pu.quantity,
        pu.quantity * pr.price AS price
    FROM       
        Purchases AS pu
    INNER JOIN 
        Products AS pr
    ON         
        pu.product_id = pr.product_id 
)
SELECT * FROM details_by_invoices;

Explanation:

• INNER JOIN:
  • Combines each purchase (pu) with its corresponding product (pr) based on product_id.
• Calculate Subtotal (price):
  • Multiplies quantity by price to compute the subtotal for each product in an invoice.
• Common Table Expression (CTE) details_by_invoices:
  • Stores the resulting data for further processing.

Intermediate Output After Step 1 (details_by_invoices):

+------------+------------+----------+-------+
| invoice_id | product_id | quantity | price |
+------------+------------+----------+-------+
| 1          | 1          | 2        | 200   |
| 3          | 2          | 1        | 200   |
| 2          | 2          | 3        | 600   |
| 2          | 1          | 4        | 400   |
| 4          | 1          | 10       | 1000  |
+------------+------------+----------+-------+

Step 2: Aggregate Subtotals to Determine Total Price per Invoice

Calculate the total price for each invoice by summing the subtotals of all products within that invoice.

SQL Snippet:

WITH details_by_invoices AS
(
    SELECT     
        pu.invoice_id,
        pu.product_id,
        pu.quantity,
        pu.quantity * pr.price AS price
    FROM       
        Purchases AS pu
    INNER JOIN 
        Products AS pr
    ON         
        pu.product_id = pr.product_id 
), 
invoice_with_max_total AS
(
    SELECT   
        invoice_id
    FROM     
        details_by_invoices
    GROUP BY 
        invoice_id
)

Explanation:

• GROUP BY invoice_id:
  • Aggregates all products within the same invoice.
• SUM(price) AS total_price:
  • Calculates the total price for each invoice by summing the subtotals of its products.
• Common Table Expression (CTE) invoice_with_max_total:
  • Identifies the invoice with the highest total price, handling ties by selecting the smallest invoice_id.

Intermediate Output After Step 2:

+------------+-------------+
| invoice_id | total_price |
+------------+-------------+
| 1          | 200         |
| 2          | 1000        |
| 3          | 200         |
| 4          | 1000        |
+------------+-------------+

Step 3: Identify the Invoice with the Highest Total Price (invoice_with_max_total)

Determine which invoice has the highest total_price. In the case of a tie, select the invoice with the smallest invoice_id.

SQL Snippet:

WITH details_by_invoices AS
(
    SELECT     
        pu.invoice_id,
        pu.product_id,
        pu.quantity,
        pu.quantity * pr.price AS price
    FROM       
        Purchases AS pu
    INNER JOIN 
        Products AS pr
    ON         
        pu.product_id = pr.product_id 
), 
invoice_with_max_total AS
(
    SELECT   
        invoice_id
    FROM     
        details_by_invoices
    GROUP BY 
        invoice_id
    ORDER BY 
        SUM(price) DESC,
        invoice_id ASC 
    LIMIT 1
)
SELECT * FROM invoice_with_max_total;

Explanation:

• ORDER BY SUM(price) DESC, invoice_id ASC:
  • Sorts the invoices first by descending total_price to prioritize higher totals.
  • In case of a tie in total_price, sorts by ascending invoice_id to select the smallest ID.
• LIMIT 1:
  • Ensures that only the top invoice is selected based on the sorting criteria.

Intermediate Output After Step 3 (invoice_with_max_total):

+------------+
| invoice_id |
+------------+
| 2          |
+------------+

Step 4: Retrieve Detailed Information of the Selected Invoice

Fetch the product_id, quantity, and subtotal (price) for each product within the selected invoice.

SQL Snippet:

WITH details_by_invoices AS
(
    SELECT     
        pu.invoice_id,
        pu.product_id,
        pu.quantity,
        pu.quantity * pr.price AS price
    FROM       
        Purchases AS pu
    INNER JOIN 
        Products AS pr
    ON         
        pu.product_id = pr.product_id 
), 
invoice_with_max_total AS
(
    SELECT   
        invoice_id
    FROM     
        details_by_invoices
    GROUP BY 
        invoice_id
    ORDER BY 
        SUM(price) DESC,
        invoice_id ASC 
    LIMIT 1
)
SELECT 
    product_id,
    quantity,
    price
FROM   
    details_by_invoices
WHERE  
    invoice_id IN
    (
        SELECT invoice_id
        FROM invoice_with_max_total
    )
ORDER BY 
    product_id;

Explanation:

• WHERE invoice_id IN (SELECT invoice_id FROM invoice_with_max_total):
  • Filters the details_by_invoices to include only the selected invoice.
• ORDER BY product_id:
  • Sorts the output by product_id for organized presentation.

Final Output:

+------------+----------+-------+
| product_id | quantity | price |
+------------+----------+-------+
| 1          | 4        | 400   |
| 2          | 3        | 600   |
+------------+----------+-------+

Explanation of Output:

• Product 1:

  • Quantity: 4
  • Price per unit: $100
  • Subtotal: 4 × $100 = $400

• Product 2:

  • Quantity: 3
  • Price per unit: $200
  • Subtotal: 3 × $200 = $600

• Total for Invoice 2: $400 + $600 = $1000

Note:

• Invoice 4 also has a total_price of $1000, but Invoice 2 is selected because it has the smaller invoice_id.