Database schema and query to accumualte total amount of days for each "category" of an animal over a period of time

I am trying to design a PostgreSQL database using Supabase to store information about animals and their categoriee, like age and gender, which will be used to define the category.

For example: The category of a young male horse would be a colt while a young female horse would be a filly. For adult horses the categories would be different and could be influenced by other factors such as castration state etc.

I need a database to query a selected period of time and get back the total amount of days of each category. So if for the time frame of 40 days there were 5 colts and 3 fillies in the database, the result of the query would be

Colts 5 * 40 = 200 
Fillies 3 * 40 = 120  

My first attempt used an SQLite database and native Android development. I had a table for the animals and one for the transfers between categories. The transfers table had the initial category of the animal and the new category as well as what type of transfer it was (birth, death, purchase, sale etc.).

The query first gets the quantity of animals in each category at the start date, then alter the quantities based on the transfers table for following days and add those numbers up. Apart from getting the data out of the database using simple select statements, everything was done using Kotlin and it took a noticeable amount of time if done on an older device or emulator, and since I plan to use Supabase I am trying to come up with a solution which would do all the computing on the back end.

This has three tables. One is for animals, which would only store age, gender and some other relevant information such as castration state etc. A second stores the categories with some information about them (name, description, etc..) and the third stores the information about the transfers between the categories. I store the id of the animal, the category, the date on which it was transferred to this category all as non-nullable values and the date on which it was transferred out of this category as a nullable value.

I don't know how to query to get the quantities for each category over a period of time. My idea is to find the difference between start_date and end_date (substitute end_date with selected end_date if it is null) in days and add these numbers up for each category.

What is good and bad about that solution?

How could I structure the SQL statement?

I think you can get what you want just by looking at the transfers table. Just count the days between start and enddate, limited by the input_start_date and input_end_date you give as parameters, and sum them together grouped by the category_id

SELECT category_id,
       Sum(Max(( Min(end_date, input_end_date) -
                 Max(start_date, input_start_date) ), 0
           ))
FROM   transfers
GROUP  BY category_id