Compute Total Cost from Two Tables (SQL Aggregation + Join) with Tiered Fees Follow-up

Problem: Compute Total Cost from Two Tables (and a Tiered Fee Follow-up) You are given two relational tables used to compute each order’s total_cost. Table schemas Table 1: order_items | Column | Type | Meaning | |---|---|---| | order_id | int | Order ID | | item_id | int | Item/Product ID | | quantity | int | Quantity purchased | | unit_price | decimal | Unit price | Table 2: order_fees | Column | Type | Meaning | |---|---|---| | order_id | int | Order ID | | fee_type | string | Fee type (e.g., shipping, service) | | fee_amount | decimal | Fee amount (an order may have multiple rows) | Task 1: Compute total_cost per order Return one row per order_id with: order_id items_cost = SUM(quantity * unit_price) fees_cost = SUM(fee_amount) total_cost = items_cost + fees_cost If an order has no rows in order_fees, treat fees_cost as 0. Follow-up: What if fees are tiered? Replace order_fees with two tables: Table 2A: order_fee_usage | Column | Type | Meaning | |---|---|---| | order_id | int | Order ID | | usage | int | Usage amount to compute a service fee | Table 2B: fee_tiers | Column | Type | Meaning | |---|---|---| | tier_start | int | Inclusive tier start | | tier_end | int | Inclusive tier end (NULL means no upper bound) | | rate | decimal | Price per usage unit within this tier | For each order, compute tier_fee from usage and return: order_id items_cost tier_fee total_cost = items_cost + tier_fee Assume incremental tiering: if usage spans multiple tiers, charge each tier’s portion at that tier’s rate and sum them up. Example Input order_items: (1, 10, 2, 3.50) (1, 11, 1, 10.00) (2, 12, 4, 2.00) order_fees: (1, shipping, 5.00) (1, service, 1.50) (2, shipping, 3.00) Output order_id=1 items_cost=17.00 fees_cost=6.50 total_cost=23.50 order_id=2 items_cost=8.00 fees_cost=3.00 total_cost=11.00