Customers above average spend

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

A finance team wants to find the big spenders: customers who spent more than the average customer, and how much of the company's revenue they represent. Using customers and lb_orders: first calculate each customer's total_spent (the sum of their orders). Then keep only customers whose total is strictly greater than the average of those totals (the average is taken over customers who have at least one order). For these customers, return name, total_spent, spend_rank (RANK by total_spent, highest first) and pct_of_company_revenue, which is ROUND(100.0 * total_spent / SUM(total_spent) OVER (), 2). Sort by spend_rank and then name.

Requirements

• Calculate each customer's total spend.
• Keep customers with a total strictly greater than the average total.
• Rank them with RANK, from the highest total.
• Calculate the percent of revenue with a window SUM over the kept rows.
• Sort by spend_rank and then name.

Constraints

• The tables are customers and lb_orders.
• The average is across customers who ordered, so use the totals CTE and not the whole customers table.
• Use CTEs: one for the totals, one for the customers above average.

Examples

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