Welcome to Subscribe On Youtube

2362. Generate the Invoice

Description

Table: Products

+-------------+------+
| Column Name | Type |
+-------------+------+
| product_id  | int  |
| price       | int  |
+-------------+------+
product_id is the primary key for this table.
Each row in this table shows the ID of a product and the price of one unit.

 

Table: Purchases

+-------------+------+
| Column Name | Type |
+-------------+------+
| invoice_id  | int  |
| product_id  | int  |
| quantity    | int  |
+-------------+------+
(invoice_id, product_id) is the primary key for this table.
Each row in this table shows the quantity ordered from one product in an invoice. 

 

Write an SQL query to show the details of the invoice with the highest price. If two or more invoices have the same price, return the details of the one with the smallest invoice_id.

Return the result table in any order.

The query result format is shown in the following example.

 

Example 1:

Input: 
Products table:
+------------+-------+
| product_id | price |
+------------+-------+
| 1          | 100   |
| 2          | 200   |
+------------+-------+
Purchases table:
+------------+------------+----------+
| invoice_id | product_id | quantity |
+------------+------------+----------+
| 1          | 1          | 2        |
| 3          | 2          | 1        |
| 2          | 2          | 3        |
| 2          | 1          | 4        |
| 4          | 1          | 10       |
+------------+------------+----------+
Output: 
+------------+----------+-------+
| product_id | quantity | price |
+------------+----------+-------+
| 2          | 3        | 600   |
| 1          | 4        | 400   |
+------------+----------+-------+
Explanation: 
Invoice 1: price = (2 * 100) = 200
Invoice 2: price = (4 * 100) + (3 * 200) = 1000
Invoice 3: price = (1 * 200) = 200
Invoice 4: price = (10 * 100) = 1000

The highest price is 1000, and the invoices with the highest prices are 2 and 4. We return the details of the one with the smallest ID, which is invoice 2.

Solutions

  • # Write your MySQL query statement below
    WITH
        P AS (
            SELECT *
            FROM
                Purchases
                JOIN Products USING (product_id)
        ),
        T AS (
            SELECT invoice_id, SUM(price * quantity) AS amount
            FROM P
            GROUP BY invoice_id
            ORDER BY 2 DESC, 1
            LIMIT 1
        )
    SELECT product_id, quantity, (quantity * price) AS price
    FROM
        P
        JOIN T USING (invoice_id);
    
    

All Problems

All Solutions