Project: Customers in C#

Time to put the module together. The shop wants a small API for its customers. GET /customers lists them, and GET /customers/{id} shows one of them, with how many orders they placed and how much they spent. Your Query and QueryOne helpers do the talking to the database.

Numbers worked out from tables: the order count and the money spent are not stored anywhere. Every request works them out from orders joined with order_items, with COUNT and SUM, so they are always up to date. Watch for two traps. A JOIN gives one row per item, so count orders with COUNT(DISTINCT ...). SUM of no rows is NULL, so wrap it in COALESCE, which gives the first of its values that isn't NULL. For example, here is a route for the stock of one category:

app.MapGet("/categories/{name}/stock", (string name) => {
    var cmd = db.CreateCommand();
    cmd.CommandText = "SELECT COUNT(*) AS products, COALESCE(SUM(stock), 0) AS in_stock FROM products WHERE category = $name";
    cmd.Parameters.AddWithValue("$name", name);
    var totals = QueryOne(cmd);
    return Results.Json(totals);
});

A category with no products gets "in_stock": 0, not null. The long SQL of the customer route goes in a variable named query, built in pieces: C# names are case-sensitive, so query never clashes with your helper Query. Your two routes must answer:

GET /customers     →  200  [{"id": 1, "name": "Ana Pop", "city": "Cluj"}, {"id": 2, "name": "Ben Ortiz", "city": "Madrid"}, …, {"id": 5, "name": "Eva Rossi", "city": "Rome"}]
GET /customers/1   →  200  {"id": 1, "name": "Ana Pop", "email": "ana@example.com", "city": "Cluj", "joined": "2026-01-10", "orders": 2, "spent": 75}
GET /customers/2   →  200  {"id": 2, "name": "Ben Ortiz", …, "orders": 1, "spent": 29}
GET /customers/3   →  200  {"id": 3, "name": "Chloe Ng", …, "orders": 2, "spent": 89}
GET /customers/5   →  200  {"id": 5, "name": "Eva Rossi", …, "orders": 0, "spent": 0}
GET /customers/99  →  404  {"error": "Customer not found"}
POST /orders {"customer_id": 5, "items": [{"product_id": 1, "quantity": 2}, {"product_id": 4, "quantity": 3}]}  →  201  {"id": 8}
GET /customers/5   →  200  {"id": 5, "name": "Eva Rossi", …, "orders": 1, "spent": 30}

Your task: at the end of the file, fill in the two customer routes. GET /customers answers the id, name and city of every customer. GET /customers/{id} answers the whole customer plus orders and spent (cancelled orders don't count), or a 404 when no customer has that id.

Assignment
Follow the Coding Tutorial to build the two customer routes on top of your Query and QueryOne helpers.

Hint
Read the customer with QueryOne and check for null first. The totals also come back as one row, because COUNT and SUM turn all the rows into one. Copy its "orders" and "spent" into the customer's dictionary before you return it.