Conditional category revenue

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Aggregation. About 12 minutes. Part of the Pro drill bank.

A shop wants one quick line that compares two categories. From lb_orders, return a single row with 2 columns: electronics_revenue, the sum of amount for orders in the 'Electronics' category, and clothing_revenue, the sum of amount for orders in the 'Clothing' category. Orders in any other category are ignored. Both numbers must be calculated in the same query and returned in the same row.

Requirements

• Return exactly 1 row with 2 columns.
• electronics_revenue adds up amount only for Electronics rows.
• clothing_revenue adds up amount only for Clothing rows.
• Other categories must not be counted in either column.

Constraints

• The table is lb_orders.
• Use SUM(CASE WHEN product_category = '...' THEN amount ELSE 0 END). This is called conditional aggregation.
• Return only these 2 columns, in this order: electronics_revenue, clothing_revenue.

Examples

Input: lb_orders product_category | amount Electronics | 1200 Clothing | 35 Electronics | 40 Electronics | 500 Electronics | 50 Electronics | 55 Output: electronics_revenue | clothing_revenue 1845 | 35 Why this passes: All Electronics amounts sum to 1845; Shirt is the only Clothing row.

Topics: lakebench, sql, conditional agg, case.

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