SQL data engineering interview problem. Difficulty: intermediate. Pattern: CTEs. About 12 minutes. Part of the Pro drill bank.
Using customers and lb_orders: 1. Compute each customer's total_spent. 2. Keep customers whose total_spent is strictly greater than the average customer total_spent (average across customers who ordered). 3. Return name, total_spent, spend_rank = RANK() by total_spent descending, pct_of_company_revenue = ROUND(100.0 * total_spent / SUM(total_spent) OVER (), 2) Order by spend_rank, name.
Input: customers + lb_orders (customer totals) name | total_spent Ana | 1275 Ben | 500 Cam | 105 Output: name | total_spent | spend_rank | pct_of_company_revenue Ana | 1275 | 1 | 100.0 Why this passes: Average spend across ordering customers is 626.67, so only Ana clears the bar. pct is of the above-average subset.
Topics: lakebench, sql, cte, rank.
More SQL interview questions · All interview problems · Learn data engineering
Interview-style drill: Multi-CTE: spenders above average with rank and revenue share.
Using `customers` and `lb_orders`: 1. Compute each customer's `total_spent`. 2. Keep customers whose total_spent is strictly greater than the average customer total_spent (average across customers who ordered). 3. Return `name`, `total_spent`, `spend_rank` = RANK() by total_spent descending, `pct_of_company_revenue` = ROUND(100.0 * total_spent / SUM(total_spent) OVER (), 2) Order by `spend_rank`, `name`.