1068. Product Sales Analysis I

Problem¶

Given Sales and Product, return one row per sale with the product name, sale year, and unit price.

Input:

# Sales
--------+------------+------+----------+------
sale_id | product_id | year | quantity | price
--------+------------+------+----------+------
1       | 100        | 2008 | 10       | 5000
2       | 100        | 2009 | 12       | 5000
7       | 200        | 2011 | 15       | 9000

# Product
-----------+-------------
product_id | product_name
-----------+-------------
100        | Nokia
200        | Apple
300        | Samsung

Output:

-------------+------+------
product_name | year | price
-------------+------+------
Nokia        | 2008 | 5000
Nokia        | 2009 | 5000
Apple        | 2011 | 9000

Key trick¶

Join Sales to Product on product_id, then project only product_name, year, and price.

Trap¶

Do not aggregate.
Do not multiply quantity * price; the requested price is unit price.
Do not return unused products such as Samsung.
Use a join that preserves all Sales rows; LEFT JOIN is safest.

Why is it interesting?¶

This is a minimal join problem that checks whether you understand foreign keys, row preservation, and column projection.

SQL solution¶

-- SQLite
-- Keep every sale row, attach its product name, then return only requested columns.
SELECT
    p.product_name,
    s.year,
    s.price
FROM Sales AS s
LEFT JOIN Product AS p
    ON s.product_id = p.product_id;

Pandas solution¶

import pandas as pd

def sales_analysis(sales: pd.DataFrame, product: pd.DataFrame) -> pd.DataFrame:
    # Left merge preserves one output row for every row in sales.
    return (
        sales
        .merge(product, on="product_id", how="left")
        [["product_name", "year", "price"]]
    )

Pytest test¶

import sqlite3

import pandas as pd
import pytest


SQL_QUERY = """
SELECT
    p.product_name,
    s.year,
    s.price
FROM Sales AS s
LEFT JOIN Product AS p
    ON s.product_id = p.product_id;
"""


def sales_analysis(sales: pd.DataFrame, product: pd.DataFrame) -> pd.DataFrame:
    return (
        sales
        .merge(product, on="product_id", how="left")
        [["product_name", "year", "price"]]
    )


def run_sql(sales_rows, product_rows):
    con = sqlite3.connect(":memory:")

    con.execute(
        """
        CREATE TABLE Sales (
            sale_id INTEGER,
            product_id INTEGER,
            year INTEGER,
            quantity INTEGER,
            price INTEGER
        );
        """
    )
    con.execute(
        """
        CREATE TABLE Product (
            product_id INTEGER,
            product_name TEXT
        );
        """
    )

    con.executemany(
        """
        INSERT INTO Sales (sale_id, product_id, year, quantity, price)
        VALUES (?, ?, ?, ?, ?);
        """,
        sales_rows,
    )
    con.executemany(
        """
        INSERT INTO Product (product_id, product_name)
        VALUES (?, ?);
        """,
        product_rows,
    )

    return pd.read_sql_query(SQL_QUERY, con)


def normalize(df):
    return sorted(
        list(df[["product_name", "year", "price"]].itertuples(index=False, name=None))
    )


@pytest.mark.parametrize(
    "sales_rows, product_rows, expected_rows",
    [
        (
            [
                (1, 100, 2008, 10, 5000),
                (2, 100, 2009, 12, 5000),
                (7, 200, 2011, 15, 9000),
            ],
            [
                (100, "Nokia"),
                (200, "Apple"),
                (300, "Samsung"),
            ],
            [
                ("Nokia", 2008, 5000),
                ("Nokia", 2009, 5000),
                ("Apple", 2011, 9000),
            ],
        ),
        (
            [
                (1, 10, 2020, 1, 100),
                (1, 10, 2021, 2, 120),
                (2, 20, 2021, 3, 300),
            ],
            [
                (10, "Book"),
                (20, "Pen"),
            ],
            [
                ("Book", 2020, 100),
                ("Book", 2021, 120),
                ("Pen", 2021, 300),
            ],
        ),
        (
            [],
            [
                (1, "OnlyProduct"),
            ],
            [],
        ),
    ],
)
def test_sales_analysis_sql_and_pandas(sales_rows, product_rows, expected_rows):
    sales = pd.DataFrame(
        sales_rows,
        columns=["sale_id", "product_id", "year", "quantity", "price"],
    )
    product = pd.DataFrame(
        product_rows,
        columns=["product_id", "product_name"],
    )
    expected = pd.DataFrame(
        expected_rows,
        columns=["product_name", "year", "price"],
    )

    sql_result = run_sql(sales_rows, product_rows)
    pandas_result = sales_analysis(sales, product)

    assert normalize(sql_result) == normalize(expected)
    assert normalize(pandas_result) == normalize(expected)

Comment on my solution¶

Your SQL and Pandas solutions are correct.

Using LEFT JOIN is a good choice because the output is defined from the Sales table, even though the foreign key means an INNER JOIN would also work under valid input.

SELECT
    p.product_name,
    s.year,
    s.price
FROM Sales AS s
LEFT JOIN Product AS p
    ON p.product_id = s.product_id;

import pandas as pd

def sales_analysis(sales: pd.DataFrame, product: pd.DataFrame) -> pd.DataFrame:
    return sales.merge(product, on="product_id", how="left")[["product_name", "year", "price"]]