Capstone – Article 12: Reporting & Aggregations (Expense Tracker)

1. Introduction

CRUD APIs are not enough for real applications. Reports and summaries are what users actually consume.

In this article, we will:

• Add aggregation queries
• Build monthly expense summaries
• Build category-wise reports
• Create dashboard-ready APIs

This turns your backend into a decision-support system.

---

2. Types of Reports We Will Build

For the logged-in user:

• Total expenses for a month
• Category-wise totals for a month
• Overall spending summary

All reports must: ✔ Respect JWT user context
✔ Be efficient (DB-level aggregation)

---

3. Why Aggregation at Database Level

❌ Aggregating in Java:

• Fetches too much data
• Slow for large datasets

✔ Aggregating in DB:

• Faster
• Scalable
• Industry best practice

We will use JPQL queries.

---

4. Creating Projection DTOs

4.1 CategorySummary DTO

package com.example.expensetracker.dto;

import java.math.BigDecimal;

public class CategorySummary {

    private String category;
    private BigDecimal totalAmount;

    public CategorySummary(String category, BigDecimal totalAmount) {
        this.category = category;
        this.totalAmount = totalAmount;
    }

    // getters
}

---

4.2 MonthlySummary DTO

package com.example.expensetracker.dto;

import java.math.BigDecimal;

public class MonthlySummary {

    private BigDecimal totalAmount;

    public MonthlySummary(BigDecimal totalAmount) {
        this.totalAmount = totalAmount;
    }

    // getters
}

---

5. Adding Aggregation Queries

Update ExpenseRepository:

@Query("""
    SELECT new com.example.expensetracker.dto.CategorySummary(
        e.category,
        SUM(e.amount)
    )
    FROM Expense e
    WHERE e.user.id = :userId
      AND MONTH(e.expenseDate) = :month
      AND YEAR(e.expenseDate) = :year
    GROUP BY e.category
""")
List<CategorySummary> getCategorySummary(
        @Param("userId") Long userId,
        @Param("month") int month,
        @Param("year") int year);

---

5.1 Monthly Total Query

@Query("""
    SELECT new com.example.expensetracker.dto.MonthlySummary(
        SUM(e.amount)
    )
    FROM Expense e
    WHERE e.user.id = :userId
      AND MONTH(e.expenseDate) = :month
      AND YEAR(e.expenseDate) = :year
""")
MonthlySummary getMonthlyTotal(
        @Param("userId") Long userId,
        @Param("month") int month,
        @Param("year") int year);

---

6. Updating ExpenseService

public List<CategorySummary> getCategorySummary(int month, int year) {

    User user = getLoggedInUser();
    return expenseRepository.getCategorySummary(user.getId(), month, year);
}

public MonthlySummary getMonthlySummary(int month, int year) {

    User user = getLoggedInUser();
    return expenseRepository.getMonthlyTotal(user.getId(), month, year);
}

private User getLoggedInUser() {
    String email = SecurityUtil.getCurrentUserEmail();
    return userRepository.findByEmail(email)
        .orElseThrow(() -> new UserNotFoundException("User not found"));
}

---

7. Creating Report Controller

@RestController
@RequestMapping("/reports")
public class ReportController {

    private final ExpenseService expenseService;

    public ReportController(ExpenseService expenseService) {
        this.expenseService = expenseService;
    }

    @GetMapping("/monthly")
    public MonthlySummary getMonthlySummary(
            @RequestParam int month,
            @RequestParam int year) {
        return expenseService.getMonthlySummary(month, year);
    }

    @GetMapping("/category")
    public List<CategorySummary> getCategorySummary(
            @RequestParam int month,
            @RequestParam int year) {
        return expenseService.getCategorySummary(month, year);
    }
}

---

8. Testing Report APIs

Examples:

GET /reports/monthly?month=1&year=2026
GET /reports/category?month=1&year=2026

Responses are dashboard-ready.

---

9. Sample Response

Category summary:

[
  { "category": "FOOD", "totalAmount": 5200.50 },
  { "category": "TRAVEL", "totalAmount": 2100.00 }
]

Monthly summary:

{
  "totalAmount": 7300.50
}

---

10. Why This Is Interview Gold

This article shows: ✔ Aggregation skills
✔ JPQL knowledge
✔ Performance awareness
✔ Real reporting logic

Very few candidates go this far.

---

11. Common Mistakes

❌ Aggregating in Java streams
❌ Forgetting user constraint
❌ Returning raw Object[]
❌ Poor API naming

---

12. Git Commit (Important)

git add .
git commit -m "Add expense reporting and aggregation APIs"

---

13. What You Should Have Now

At this point:

• Reporting APIs exist
• Monthly & category summaries work
• Backend is analytics-ready

---

14. What’s Next?

➡ Capstone – Article 13: Performance, Caching & Optimization

• Cache reports
• Improve performance
• Production tuning

Type Next when you’re ready 🚀