Since this will be long message i couldn't comment above so here's an explanation.
PrecomputedDateRange is a table, which contains all the dates that may occur in the future or past for your query. Hence you will never have a need for the generation of dates on the fly at execution time of queries. The WITH DateRange AS (.) CTE is used mostly for generating a set of dates on the fly, which consumes much more resources in case of dealing with a large amount of datasets. By precomputing the dates and storing them in a table, you avoid that computational overhead every time the query runs.
The SQL WITH clause has its uses but in your particular case it obliges the database to compute the date range for every execution of your query. This could involve a ROW_NUMBER() operation or other techniques to produce the series of dates. While this is fine for smaller datasets, it becomes pretty inefficient with large ranges since the database has to compute the entire sequence before it even starts processing the rest of the query.
But by using something like a precomputed table, as with PrecomputedDateRange, it ensures that you're not doing the date range computation over and over again. Instead, you do simple SELECT from already prepared dates stored in a table; in this case, it is extremely efficient, if you are working with large date ranges.
You'd worry about the memory cost of storing these dates, but precomputing the date range is generally a one-time operation, and the cost in memory is quite low because you're just storing dates (not huge data sets). The gain in query performance from no longer having to compute the dates for each query is likely to more than offset the extra memory usage. Moreover, this table doesn't change very often, so it's okay to keep it static for a long time.
When you join on a precomputed date table, it executes faster because dates are already materialized. Without it if you're generating the dates dynamically (via ROW_NUMBER() or other means), then the database has to execute more steps to generate and join dates, which consumes more performance.
And finally @SqlResultSetMapping will help in mapping the query result to your DTO. Once more, though, this is not supposed to be the bottleneck and rather on the Java side with respect to data processing-the processing and aggregating after fetching into lists in Java, for example.
Of course, all this can be improved with an additional step-caching the results (for example, by using Redis or Memcached)-so that you store the processed aggregation results for the most frequently requested date ranges, further improving response times.
PrecomputedDateRange is nothing but just a simple table that stores all the dates that might be needed to avoid generating dates on every query run. The cost of keeping these dates in memory should usually be very low, so the performance improvement should be ample enough to outweigh this. You can further accelerate the response time by caching results and also optimize the database query, particularly proper indexing and partitioning. hope it is all clear now!