🏆Capstone project: Project: Build the Category Capacity Report

The lending desk wants a compact capacity report for items with usual periods of fourteen days or less. A category belongs in the report only when at least two qualifying items support its summary. The busiest category by total lending days should appear first.

Open the new category_report.sql. Its starting query returns only category values. Replace it with one statement that follows this brief:

• Return category, item_count, and total_loan_days, in that order.

• Count with COUNT(*) and total loan_days with SUM.

• Let only source rows with loan_days <= 14 contribute.

• Build one group per category and keep groups with COUNT(*) >= 2.

• Order by total loan days descending, then category ascending.

This report needs fewer aggregate columns than summary.sql. Do not carry over minimum, maximum, or average simply because they are available. A useful report answers the requested question without making the reader sort through unrelated values.

Plan the two filters before writing. The period limit describes one item, so it belongs in WHERE before groups form. The minimum count describes a completed category group, so it belongs in HAVING after GROUP BY.

Then shape the answer around the reader. Select the group key first so every row names its category, follow it with the count and total, and give both aggregate values their required headings. The sort can refer to total_loan_days because that alias names the completed total in this result.

Press Run and find the project section:

== category_report.sql ==
Columns: category | item_count | total_loan_days
events | 2 | 21
tools | 2 | 10

Electronics has only one item left after the over-limit speaker is removed, so its completed group does not pass the count rule. Events comes before tools because 21 is larger than 10.

Submit uses positive fractional periods, tied totals, scrambled insertion, and a group that crosses the minimum only before its over-limit row is removed. It also tries a catalog where no group remains. Keep the three headings even when the result has no rows.

A tied total does not make either category disappear. The secondary category key only decides which tied summary row appears first, so every retained group remains represented exactly once.

If your values are correct but the rows swap, inspect both order keys. total_loan_days DESC makes larger totals first; category ASC settles equal totals predictably.

You have built a report by deciding what contributes, how rows combine, which summaries remain, and how readers receive them. Those are the central choices behind turning many database rows into a small answer someone can act on.