SQL - Return all rows before certain date for each partition

Question

I currently have two tables like so:

ID	Date
1	01-01-2022
2	01-02-2022
3	01-10-2022
4	01-03-2022

ID	Date	Action
1	01-05-2022	forward
1	12-20-2021	stop
1	10-15-2021	stop
1	01-20-2022	forward
2	12-30-2021	stop
2	12-14-2021	forward
2	12-16-2021	forward
2	12-01-2021	reverse
2	01-15-2022	forward
3	01-01-2022	reverse
3	01-02-2022	reverse
3	01-03-2022	stop
4	10-15-2021	reverse
4	03-04-2021	forward
4	01-10-2021	reverse

I am looking to return a table that consists of each ID and a count of how many actions took place before the corresponding date in the first table. So for example, ID 1 would return a count of 2 because 2 of its actions took place before 01-01-2022.

I would expect the final table to look something like:

ID	Count_action
1	2
2	4
3	3
4	2

Any tips would be greatly appreciated!

Answer 1

A left join and aggregate count function should do. Notice we join on the dates being <= date from. Left join preserves all Id's from A so we will get a 0 count on such records when no dates in the B table are prior to the date in A for the related ID.

SELECT A.ID, coalesce(count(B.*),0) Count_action
FROM A  --A is your first table with 1 date per ID.
LEFT JOIN B
  on A.ID= B.ID
  and B.Date <=A.Date
GROUP BY A.ID

Answer 2

A left join will do it:

select p.id, count(a.id) as Count_action
from parentTable p
left join actionTable a on a.id = p.id
  and a.date < p.date
group by p.id

Because count() ignores nulls, count(a.id) is used to get a count of 0 for id’s that have no actions before their date, instead of the usual count(*) which would give a count one 1 in that case.

Importantly, the condition a.date < p.date is in the join. If it were in the where, the left join would be effectively changed to an inner join, and id’s without matching actions would then not appear at all in the result.