Skip to content
Katabench
Try free

Customer Order Totals

Medium About 15 min Free, no account needed

Sum each customer's orders with a single SQL query instead of an N+1 loop.

The brief

You are given an Entity Framework Core ECommerceDbContext over two tables:

  • Customers (Id, Name, Country)
  • Orders (Id, CustomerId, Total, PlacedAt)

Implement CustomerTotals(ECommerceDbContext db, string country) to return one CustomerTotal { CustomerId, Total } for every customer in the given country, where Total is the sum of that customer's order totals. Order the result by CustomerId ascending. (Every customer in the test data has at least one order.)

The performance goal

The starter code is a classic N+1: it loads the customers, then runs a separate query per customer to sum their orders. Against the hidden dataset of thousands of customers that means thousands of round-trips to the database, and it times out.

Your goal: rewrite CustomerTotals so EF Core translates it into a single SQL query, collapsing the per-customer round-trips into one statement the database executes server-side.

This puzzle runs against a real, throwaway PostgreSQL database that is created just for your submission and destroyed afterwards. Write provider-agnostic LINQ, you do not open the connection yourself.

Starter code

C#
using System.Collections.Generic;
using System.Linq;
using CodeRunner.PuzzleSchemas;

public class Solution
{
    // Return one CustomerTotal per customer in `country`, summing their orders,
    // ordered by CustomerId ascending.
    public List<CustomerTotal> CustomerTotals(ECommerceDbContext db, string country)
    {
        // Naive N+1: one query for the customers, then one query PER customer.
        // Correct, but far too many round-trips for the hidden dataset.
        var customers = db.Customers
            .Where(c => c.Country == country)
            .OrderBy(c => c.Id)
            .ToList();

        var result = new List<CustomerTotal>();
        foreach (var c in customers)
        {
            var total = db.Orders
                .Where(o => o.CustomerId == c.Id)
                .Sum(o => o.Total);

            result.Add(new CustomerTotal { CustomerId = c.Id, Total = total });
        }

        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.

EF Core practice →

Related guides

What it practices

N+1 queries
Replace a query per row, or per key, with one set-based round trip.
Aggregation and grouping
GROUP BY, HAVING, conditional aggregates, pivots and ROLLUP or grouping sets.

Related katas