FinOps: CROSS JOIN inflates line revenue

SQL data engineering interview problem. Difficulty: advanced. Pattern: Joins. About 25 minutes. Part of the Pro drill bank.

Ticket from FinOps: 'The revenue tile is slow and the numbers are huge.' The query uses a CROSS JOIN between orders and order items, which pairs every order with every item. With thousands of orders, that makes millions of rows, and the revenue is counted many times.

Compute the daily revenue of the order lines. Join order_items to orders and sum quantity * unit_price for each day. The editor uses a CROSS JOIN. Replace it with a proper join on order_id. Exclude cancelled orders. Return order_date (the date of orders.created_at) and line_revenue, which is ROUND(SUM(quantity * unit_price), 2), ordered by order_date. To pass, the revenue must be right and the efficiency score must be at least 70.

Requirements

  • Join orders and order_items on order_id.
  • Exclude cancelled orders.
  • Sum quantity * unit_price for each day and round to 2 decimals.
  • Return order_date and line_revenue, ordered by order_date.

Constraints

  • Do not use a CROSS JOIN.
  • order_date comes from orders.created_at, cast to a date.
  • Use ROUND(SUM(i.quantity * i.unit_price), 2).

Examples

Input: order_id | order_status | quantity | unit_price ORD-0005 | paid | 2 | 50.00 ORD-0005 | paid | 1 | 30.00 ORD-0099 | cancelled | 1 | 100.00 Output: order_date | line_revenue 2026-08-01 | 130.00 Why this passes: Line revenue sums quantity * unit_price for non-cancelled orders only. CROSS JOIN pairs every order with every line item and inflates the sum.

Topics: finops, joins, cartesian.

More SQL interview questions · All interview problems · Learn data engineering

advanced

FinOps: CROSS JOIN inflates line revenue

Production ticket: Daily line revenue uses CROSS JOIN between orders and order_items; numbers and bytes explode.

Compute daily line revenue from `order_items` joined to `orders`: - order_date, line_revenue - order_date from `orders.created_at` - line_revenue is ROUND(SUM(quantity * unit_price), 2) per day - exclude cancelled orders - ordered by order_date The editor has CROSS JOIN between orders and order_items. Replace it with a proper join on order_id. Pass = correct revenue **and** efficiency score ≥ 70. No CROSS JOIN.