## Problem You have two tables: `customers(id, name)` and `orders(id, customer_id, amount, created_at)`. Write a SQL query to find the top 5 customers by total spend, along with their order count and average order value. ```sql -- customers: id INT, name VARCHAR -- orders: id INT, customer_id INT, amount DECIMAL, created_at TIMESTAMP SELECT c.name, COUNT(o.id) AS order_count, SUM(o.amount) AS total_spend, AVG(o.amount) AS avg_order_value FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.id, c.name ORDER BY total_spend DESC LIMIT 5; ``` **Example output:** ``` name | order_count | total_spend | avg_order_value ------------|-------------|-------------|---------------- Alice | 12 | 4800.00 | 400.00 Bob | 8 | 3200.00 | 400.00 ``` ## Follow-ups 1. How would you modify the query to show top spenders per month using a window function? 2. What index would you add to `orders` to speed up this aggregation? 3. Rewrite this in Python using pandas from a DataFrame -- what is the equivalent `groupby` chain? 4. How would you handle customers who have placed no orders (LEFT JOIN behavior)?

InterviewDB Experience · Los Angeles

Top Spenders: Find the Top N Customers by Total Spend Using SQL and Python

phone

Interview Experience

Problem You have two tables: customers(id, name) and orders(id, customer_id, amount, created_at). Write a SQL query to find the top 5 customers by total spend, along with their order count and average order value. Example output: Follow-ups How would you modify the query to show top spenders per month using a window function? What index would you add to orders to speed up this aggregation? Rewrite this in Python using pandas from a DataFrame -- what is the equivalent groupby chain? How would you…

Full Details

🔒

Unlock all Cloudkitchens questions

Full insider details, leaked discussions, and candidate experiences.

Get full access — $100 a year, unlimited access

About This Question

This is a candidate experience report from a cloudkitchens interview during the phone round.

It covers the following topics: Coding, Sql, Phone .

Topics

Coding Sql Phone

Top Spenders: Find the Top N Customers by Total Spend Using SQL and Python

Interview Experience

Full Details

About This Question

Topics

More Cloudkitchens Interview Questions