Query building help

Hi,

I am building a link tracking app (a url shortening service).

link

• id
• name
• created_at

tag

• id
• name
• created_at

clicks

• id
• link_id
• created_at

link_tag

• link_id
• tag_id
• created_at

Every link may have a tag (associated by a pivot table). Clicks have a link_id (which is the id of the link).

I'd like to query the database for the number of clicks a given link obtained within a date range based on a tag.

So I want to do something like this:

FOR ALL links ASSOCIATED WITH tag_id COUNT clicks WHERE created_at IS BETWEEN some_date_past AND some_date_future

Is it possible to make a query like this? Is it possible to get this data in 1 query?

Thanks,