#1068 Product Sales Analysis I - Solution

Table: Sales

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+
(sale_id, year) is the primary key (combination of columns with unique values) of this table.
product_id is a foreign key (reference column) to Product table.
Each row of this table shows a sale on the product product_id in a certain year.
Note that the price is per unit.

Table: Product

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+
product_id is the primary key (column with unique values) of this table.
Each row of this table indicates the product name of each product.

Write a solution to report the product_name, year, and price for each sale_id in the Sales table.

Return the resulting table in any order.

The result format is in the following example.

Example 1:

Input: 
Sales table:
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+ 
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
+---------+------------+------+----------+-------+
Product table:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+------------+--------------+
Output: 
+--------------+-------+-------+
| product_name | year  | price |
+--------------+-------+-------+
| Nokia        | 2008  | 5000  |
| Nokia        | 2009  | 5000  |
| Apple        | 2011  | 9000  |
+--------------+-------+-------+
Explanation: 
From sale_id = 1, we can conclude that Nokia was sold for 5000 in the year 2008.
From sale_id = 2, we can conclude that Nokia was sold for 5000 in the year 2009.
From sale_id = 7, we can conclude that Apple was sold for 9000 in the year 2011.

The key idea in #1068 Product Sales Analysis I is to combine information from two related tables: Sales and Product. The Sales table stores transaction details such as the year and price, while the Product table stores product names. Since both tables share the product_id column, you can use a SQL JOIN to merge their data.

The typical approach is to perform an INNER JOIN between the two tables using product_id as the join condition. After joining, select the relevant fields such as the product name from the Product table and the year and price from the Sales table. This ensures each sales record is enriched with its corresponding product name.

This problem mainly tests your understanding of relational databases, foreign keys, and how JOIN operations combine data across tables efficiently.

Approach	Time Complexity	Space Complexity
INNER JOIN between Sales and Product	O(n)	O(1)

Solutions (3)

Using SQL Join

We will use an SQL query with an INNER JOIN to combine the Sales and Product tables based on the product_id. INNER JOIN will ensure we fetch records that have corresponding IDs in both tables. After joining, we will project the columns product_name, year, and price.

Time Complexity: O(N) where N is the number of rows in Sales table.
Space Complexity: O(1) as we use a fixed amount of extra space for the query execution.

1SELECT P.product_name, S.year, S.price FROM Sales S INNER JOIN Product P ON S.product_id = P.product_id;

Explanation

This SQL query selects the product_name from the Product table and year and price from the Sales table. We use INNER JOIN to join these two tables based on product_id.

Using Multiple Data Structure in Code

This approach involves using programming constructs to emulate a join operation typically done in SQL. We'll utilize hash maps (or dictionaries) in different languages to store data from one table and then iterate through the other to join and construct the result.

Time Complexity: O(N + M) where N is the number of products and M is the number of sales.
Space Complexity: O(N) to store the dictionary mapping product_id to product_name.

Python C++

1#include <unordered_map>
2#include <vector>
3#include <string>
4#include <iostream>
5using namespace std;

struct Sale {
    int sale_id;
    int product_id;
    int year;
    int quantity;
    int price;
};

struct Product {
    int product_id;
    string product_name;
};

struct Result {
    string product_name;
    int year;
    int price;
};

vector<Result> productSalesAnalysis(vector<Sale>& sales, vector<Product>& products) {
    unordered_map<int, string> productMap;
    for (const auto& product : products) {
        productMap[product.product_id] = product.product_name;
    }

    vector<Result> result;
    for (const auto& sale : sales) {
        if (productMap.find(sale.product_id) != productMap.end()) {
            result.push_back({productMap[sale.product_id], sale.year, sale.price});
        }
    }
    return result;
}

int main() {
    vector<Sale> sales = {
        {1, 100, 2008, 10, 5000},
        {2, 100, 2009, 12, 5000},
        {7, 200, 2011, 15, 9000}
    };

    vector<Product> products = {
        {100, "Nokia"},
        {200, "Apple"},
        {300, "Samsung"}
    };

    vector<Result> results = productSalesAnalysis(sales, products);
    for (const auto& result : results) {
        cout << result.product_name << " " << result.year << " " << result.price << endl;
    }
    return 0;
}