Writing Complex Queries for Test Reports

The previous lessons used SQL to verify — one row, one bug, one assertion. This lesson uses SQL to report — pulling a multi-table summary together for a stakeholder who wants to see numbers, not raw rows. "How many orders shipped this week?", "Who are our top 10 customers?", "Which products never sold?" are all reports. A QA engineer who can write them well becomes the team's go-to for "can you just check…" — which is a great place to be.

A status summary — counts and percentages

A PM asks: "how is the order pipeline distributed across statuses?" One query gives a clean answer:

SELECT
    status,
    COUNT(*)                                                      AS count,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM orders), 1)    AS percentage
FROM orders
GROUP BY status
ORDER BY count DESC;

Result:

| status     | count | percentage |
|------------|-------|------------|
| completed  | 2     |       40.0 |
| pending    | 1     |       20.0 |
| shipped    | 1     |       20.0 |
| cancelled  | 1     |       20.0 |

Three SQL ideas in one query: GROUP BY for the bucket, COUNT for the size of each bucket, and a scalar subquery — (SELECT COUNT(*) FROM orders) — to compute the denominator for the percentage. The * 100.0 (not * 100) forces floating-point division so you get 40.0 instead of integer-truncated 40.

Daily order summary — using DATE() to bucket by day

SELECT
    DATE(created_at) AS order_date,
    COUNT(*)         AS orders,
    SUM(total)       AS revenue,
    AVG(total)       AS avg_order_value
FROM orders
GROUP BY DATE(created_at)
ORDER BY order_date DESC
LIMIT 30;

DATE(created_at) strips the time component, so all orders on the same day collapse into one bucket. Sample result:

| order_date | orders | revenue | avg_order_value |
|------------|--------|---------|-----------------|
| 2026-05-05 | 1      |   25.00 |          25.00  |
| 2026-05-03 | 1      |   63.00 |          63.00  |
| 2026-05-01 | 1      |  320.00 |         320.00  |
| 2026-04-28 | 1      |   48.50 |          48.50  |
| 2026-04-20 | 1      |  145.00 |         145.00  |

A small dialect tip: DATE(...) works in MySQL, PostgreSQL, SQLite. SQL Server uses CAST(created_at AS DATE).

Top customers — JOIN, GROUP BY, ORDER BY, LIMIT in one query

SELECT
    u.name,
    u.email,
    COUNT(o.id)         AS total_orders,
    SUM(o.total)        AS total_spent,
    MAX(o.created_at)   AS last_order_date
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email
ORDER BY total_spent DESC
LIMIT 10;

Result:

| name        | email             | total_orders | total_spent | last_order_date     |
|-------------|-------------------|--------------|-------------|---------------------|
| Bob Patel   | bob@example.com   | 2            |      368.50 | 2026-05-01 12:08:50 |
| Alice Khan  | alice@example.com | 1            |      145.00 | 2026-04-20 14:30:21 |
| Dan Müller  | dan@example.com   | 1            |       63.00 | 2026-05-03 17:42:30 |
| Esha Roy    | esha+qa@test.com  | 1            |       25.00 | 2026-05-05 09:11:08 |

Five ideas in one query — JOIN to combine, GROUP BY to aggregate, three aggregates side by side, ORDER BY to rank, LIMIT to cap. This is what most reporting queries look like.

Product popularity

SELECT
    p.name,
    SUM(oi.quantity)              AS units_sold,
    SUM(oi.quantity * oi.price)   AS revenue
FROM products p
JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id, p.name
ORDER BY units_sold DESC;

Result (sample, ranked by units sold):

| name                     | units_sold | revenue |
|--------------------------|------------|---------|
| Cable Organiser          | 4          |   32.00 |
| Standing Desk Mat        | 2          |  110.00 |
| Wireless Mouse           | 3          |   75.00 |
| Mechanical Keyboard Pro  | 1          |  120.00 |
| ...                      | ...        |     ... |

A LEFT JOIN would also include products that never sold (units_sold = 0); the inner JOIN here drops them. Pick the variant that matches the question. If the PM wants every product including the duds, use LEFT JOIN.

CASE — branching logic in the SELECT

CASE is SQL's if/else. Bucket products into price tiers:

SELECT
    name,
    price,
    CASE
        WHEN price < 20  THEN 'Budget'
        WHEN price < 50  THEN 'Mid-range'
        WHEN price < 100 THEN 'Premium'
        ELSE 'Luxury'
    END AS price_tier
FROM products
ORDER BY price;

Result:

| name                     | price  | price_tier |
|--------------------------|--------|------------|
| Cable Organiser          |   8.00 | Budget     |
| SQL for Testers (book)   |  18.50 | Budget     |
| Wireless Mouse           |  25.00 | Mid-range  |
| Clean Code (book)        |  30.00 | Mid-range  |
| Desk Lamp                |  42.00 | Mid-range  |
| USB-C Hub                |  45.00 | Mid-range  |
| Standing Desk Mat        |  55.00 | Premium    |
| Webcam HD                |  75.00 | Premium    |
| Mechanical Keyboard Pro  | 120.00 | Luxury     |
| 27-inch Monitor          | 320.00 | Luxury     |

CASE works in WHERE, ORDER BY, and inside aggregates too. A particularly useful pattern is conditional counting:

-- Orders by status, in a single row instead of multiple groups
SELECT
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
    SUM(CASE WHEN status = 'pending'   THEN 1 ELSE 0 END) AS pending,
    SUM(CASE WHEN status = 'shipped'   THEN 1 ELSE 0 END) AS shipped,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
    COUNT(*)                                              AS total
FROM orders;

Result:

| completed | pending | shipped | cancelled | total |
|-----------|---------|---------|-----------|-------|
| 2         | 1       | 1       | 1         | 5     |

One row, one big-picture summary — perfect for a Slack message or a dashboard tile.

COALESCE — handle NULLs in the result

COALESCE(a, b, c, …) returns the first non-NULL argument. Useful when an outer-joined column would otherwise be NULL:

SELECT
    u.name,
    COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY total_spent DESC;

Without COALESCE, Carol (who has no orders) would show total_spent = NULL. With it, she shows 0 — much friendlier in a report row.

A QA-friendly health-check dashboard

Pull it all together. A single SELECT that gives a one-row "system snapshot":

SELECT
    (SELECT COUNT(*) FROM users)                                 AS total_users,
    (SELECT COUNT(*) FROM users WHERE is_active = TRUE)          AS active_users,
    (SELECT COUNT(*) FROM products)                              AS total_products,
    (SELECT COUNT(*) FROM products WHERE in_stock = TRUE)        AS in_stock,
    (SELECT COUNT(*) FROM orders)                                AS total_orders,
    (SELECT COUNT(*) FROM orders WHERE status = 'completed')     AS completed_orders,
    (SELECT COALESCE(SUM(total), 0) FROM orders)                 AS total_revenue;

That one row is the kind of "is everything sensible?" snapshot you can drop into a release announcement, a status post, or a daily standup. Built from queries you can write today.

⚠️ Common Mistakes

• COUNT(*) * 100 / total returning an integer. Integer division truncates — 40, not 40.0. Multiply by 100.0 (or CAST(... AS FLOAT)) to force float math.
• JOIN dropping the rows you wanted to count. JOIN order_items excludes products that never sold; LEFT JOIN includes them with COUNT 0. The choice changes the report — pick the right join for the question.
• Selecting columns that aren't in GROUP BY or an aggregate. Lax MySQL settings let it slide; PostgreSQL refuses; SQL Server refuses. Always pin every SELECT column down — group by it or aggregate it.

🎯 Practice Task

30 minutes. Build real reports from the e-commerce database.

1. Build a status-distribution report for orders. Columns: status, count, percentage. Round percentage to one decimal.
2. Build a top-customers report — name, total orders, total spent, last order date — sorted by total spent. Include users with zero orders (LEFT JOIN + COALESCE).
3. Build a product-popularity report including products that have never sold. Sort by units sold descending; products with zero sales appear last.
4. Use CASE to add a "price tier" column to every product. Pick your own tier boundaries. Run a SELECT that groups by tier and counts how many products are in each.
5. Use conditional counting (SUM(CASE WHEN ... THEN 1 ELSE 0 END)) to produce a one-row order summary: completed, pending, shipped, cancelled, total.
6. Build a daily-orders report grouped by DATE(created_at). Include count, revenue, average order value.
7. Stretch: assemble a single multi-line report that you'd send to a PM at the end of a sprint: top 5 customers, top 5 products, orders by status, and the system-snapshot row from the lesson. Run all four queries; copy the results into a doc and admire your handiwork. That is a tester who got promoted by writing useful SQL.

That wraps up Chapter 5. You can now back up any test you write with a database query that confirms the truth. Chapter 6 covers the advanced techniques you'll meet less often but should recognise on sight — subqueries, window functions, and the views/stored-procedures/triggers ecosystem developers build on top of plain SQL.