Compute costs with validation and sorting in Python

Implement a three-part Python task to compute costs for purchase line items. Part 1: Write compute_cost(line_items, price_db) where line_items is a list of dicts like {"product_id": str, "qty": int} and price_db is a dict mapping product_id -> unit_price (float). Return (total_cost, breakdown) where breakdown lists per-item cost. Clarify and implement behavior when a product_id is missing from price_db (e.g., raise, skip with warning, or default). Part 2: Add robust validation: quantities/prices must be numeric; qty must be nonnegative; reject NaN/inf; detect and report invalid rows with clear errors. Include tests for empty input, large values, and duplicate product_ids (define whether to sum or treat as separate lines). Part 3: If sort=True, return breakdown sorted by per-item cost descending using a lambda key; otherwise preserve input order. Ensure outputs match expected results and document rounding rules.

Compute line-item and total purchase cost

You are given two tables: products with unit prices, and line_items representing a shopping cart. Write a query to compute, for each line item that has a matching product, its unit price, its line cost (qty * unit_price), and the overall total cost across all such line items.

Requirements:

• Treat the products table as the price database.
• Only include line_items whose product_id exists in products (i.e., skip missing product_ids by using an inner join).
• Do not perform any additional validation (negative quantities and invalid prices are allowed in this part).
• Return one row per joined line item, with a window column total_cost that repeats the overall sum on every row.
• Order the result by line_id ascending.

Table products

column	type	constraints
product_id	VARCHAR(10)	PRIMARY KEY
unit_price	DECIMAL(10,2)

Sample data:

product_id	unit_price
P1	10
P2	5.5
P3	20
P4	NULL
P5	-3

Table line_items

column	type	constraints
line_id	INT	PRIMARY KEY
product_id	VARCHAR(10)
qty	INT

Sample data:

line_id	product_id	qty
1	P1	2
2	P2	3
3	P99	1
4	P3	0
5	P2	-1
6	P1	4
7	P4	2
8	P5	1

Expected output

One row per line item that has a matching product, including unit price, line cost (qty * unit_price), and a total_cost column repeating the overall sum across all such rows. Rows are ordered by line_id ascending.

line_cost	line_id	product_id	qty	total_cost	unit_price
20	1	P1	2	68	10
16.5	2	P2	3	68	5.5
0	4	P3	0	68	20
-5.5	5	P2	-1	68	5.5
40	6	P1	4	68	10
NULL	7	P4	2	68	NULL
-3	8	P5	1	68	-3

Hints

1. Start by joining line_items to products on product_id to get the unit_price for each line.
2. Use a window function SUM(...) OVER() to compute the overall total_cost and repeat it on every row.

Validate line items and report invalid rows

Using the same products and line_items tables, write a query that performs robust validation of each line item and reports invalid rows with clear error messages.

Validation rules:

• A line is INVALID if its product_id does not exist in products.
• A line is INVALID if qty is NULL or negative.
• A line is INVALID if unit_price is NULL or negative.
• Otherwise the line is VALID.

For every line_items row, return:

• line_id, product_id, qty, unit_price
• validity_status: 'VALID' or 'INVALID'
• error_reason: a short text such as 'Unknown product_id', 'Missing qty', 'Negative qty', 'Missing unit_price', or 'Negative unit_price' (pick the first applicable reason in that priority order)
• line_cost: qty * unit_price for VALID rows, and NULL for INVALID rows.

Treat duplicate product_ids across different line_ids as separate lines; you do not need to aggregate them here. Order the result by line_id ascending.

Table products

column	type	constraints
product_id	VARCHAR(10)	PRIMARY KEY
unit_price	DECIMAL(10,2)

Sample data:

product_id	unit_price
P1	10
P2	5.5
P3	20
P4	NULL
P5	-3

Table line_items

column	type	constraints
line_id	INT	PRIMARY KEY
product_id	VARCHAR(10)
qty	INT

Sample data:

line_id	product_id	qty
1	P1	2
2	P2	3
3	P99	1
4	P3	0
5	P2	-1
6	P1	4
7	P4	2
8	P5	1

Expected output

One row per line_items entry with validation status, a specific error_reason for invalid lines, and line_cost only for valid lines. Rows are ordered by line_id ascending.

error_reason	line_cost	line_id	product_id	qty	unit_price	validity_status
NULL	20	1	P1	2	10	VALID
NULL	16.5	2	P2	3	5.5	VALID
Unknown product_id	NULL	3	P99	1	NULL	INVALID
NULL	0	4	P3	0	20	VALID
Negative qty	NULL	5	P2	-1	5.5	INVALID
NULL	40	6	P1	4	10	VALID
Missing unit_price	NULL	7	P4	2	NULL	INVALID
Negative unit_price	NULL	8	P5	1	-3	INVALID

Hints

1. Use a LEFT JOIN from line_items to products so that you can detect unknown product_ids as NULLs on the products side.
2. Implement the validation logic with CASE expressions to derive both validity_status and a human-readable error_reason.

Sort validated line items by cost with a flag

Using the same products and line_items tables plus a query_params table, return a cost breakdown of only VALID line items (as defined in Question 2) and optionally sort the breakdown by per-item cost.

Definitions for this query:

• A line is VALID if:
  • product_id exists in products (inner join succeeds), and
  • qty is non-negative (qty >= 0), and
  • unit_price is not NULL and non-negative (unit_price >= 0).

Requirements:

• Return line_id, product_id, qty, unit_price, and line_cost (qty * unit_price) for only VALID lines.
• The query_params table contains a single row with a column sort_flag (0 or 1).
• If sort_flag = 1, sort the breakdown by line_cost in descending order.
• If sort_flag = 0, preserve the original input order, which is represented by line_id ascending.
• Implement the conditional ordering logic in a single query using ORDER BY; do not write two separate queries.
• Round line_cost to 2 decimal places (use DECIMAL(10,2) arithmetic).

Assume the provided sample_data has sort_flag = 1, so the sample output should be sorted by line_cost descending.

Table products

column	type	constraints
product_id	VARCHAR(10)	PRIMARY KEY
unit_price	DECIMAL(10,2)

Sample data:

product_id	unit_price
P1	10
P2	5.5
P3	20
P4	NULL
P5	-3

Table line_items

column	type	constraints
line_id	INT	PRIMARY KEY
product_id	VARCHAR(10)
qty	INT

Sample data:

line_id	product_id	qty
1	P1	2
2	P2	3
3	P99	1
4	P3	0
5	P2	-1
6	P1	4
7	P4	2
8	P5	1

Table query_params

column	type	constraints
sort_flag	INT	NOT NULL

Sample data:

sort_flag
1

Expected output

One row per VALID line item (product exists, qty >= 0, unit_price not NULL and >= 0), with line_cost, ordered by line_cost descending because sort_flag = 1 in the sample_data.

line_cost	line_id	product_id	qty	unit_price
40	6	P1	4	10
20	1	P1	2	10
16.5	2	P2	3	5.5
0	4	P3	0	20

Hints

1. Filter to VALID rows using a WHERE clause that encodes the same rules from the validation step (inner join plus non-negative qty and unit_price).
2. To switch between sorted and original order in a single query, join to query_params and use CASE expressions inside ORDER BY based on sort_flag.