Customers Without Orders
Medium About 15 min Free, no account needed
Find customers who never ordered with one anti-join instead of an N+1 loop.
The brief
Using the ECommerceDbContext (tables Customers and Orders), implement
CustomersWithoutOrders(ECommerceDbContext db, string country) to return the
ids of every customer in the given country who has no orders at all,
ordered by Id ascending.
The starter code is an N+1: it loads the customers, then asks the database "any orders?" once per customer. Against thousands of customers that is thousands of round-trips, and it times out.
Your goal: express it as a single translated query (an anti-join), so the database identifies the order-less customers in one round-trip instead of the N+1 the starter performs.
This puzzle runs against a real, throwaway PostgreSQL database created just for your submission and destroyed afterwards.
Starter code
using System.Collections.Generic;
using System.Linq;
using CodeRunner.PuzzleSchemas;
public class Solution
{
// Return the ids (ascending) of customers in `country` that have no orders.
public List<int> CustomersWithoutOrders(ECommerceDbContext db, string country)
{
// Naive N+1: load the customers, then probe orders once per customer.
var customers = db.Customers
.Where(c => c.Country == country)
.OrderBy(c => c.Id)
.ToList();
var result = new List<int>();
foreach (var c in customers)
{
bool hasOrders = db.Orders.Any(o => o.CustomerId == c.Id);
if (!hasOrders) result.Add(c.Id);
}
return result;
}
}How it is graded
Your LINQ or SQL runs against real PostgreSQL. You see the SQL your code generates on the sample tests and can inspect its execution plan, so you learn when the database did the wrong work.
What it practices
- Semi-joins and anti-joins
- Ask existence questions with EXISTS, NOT EXISTS or Any instead of loading or counting rows.
- N+1 queries
- Replace a query per row, or per key, with one set-based round trip.
Related katas
- Count Active Devices Medium Pro Collapse a per-device existence probe into one server-side count under a memory cap.
- Who Bought This Show Medium Pro Find a show's ticket buyers in one statement instead of a query per booking.
- Customer Order Totals Medium Free Sum each customer's orders with a single SQL query instead of an N+1 loop.
- Top Spending Customers Medium Free Return the highest-spending customers with one ranked query, not an N+1.