I have an 'actions' table in the following format:

which is in a one-to-many relationship with 'categories' table (one category can have many actions, category_id is the foreign key above) in the following format:

I need an API that receives a date range (start and end values) as an input an should return the total number of actions for all categories that take place for every day within that range, like so:

Basically showing how many actions of a particular category took place per day in that date range.

My initial way of solving this was to fetch the data grouped by date and then manipulate that in the code. But I want to know if there's a way to do achieve this with SQL itself.

CodePudding user response：

select   created_date                              as day
        ,count(case when name = 'Cat-1'then 1 end) as 'Cat-1'
        ,count(case when name = 'Cat-2'then 1 end) as 'Cat-2'
from     t join t2 using(id)
group by created_date

Fiddle