It sounds so simple but I can't figure it out. I have 2 tables:

TABLE 1 contains a list of projects with the dates at which they were approved.

TABLE 2 contains dates when tests were performed on these projects.

THIS IS WHAT I NEED: For each project, I want to count the total number of tests prior to each APPROVAL_DATE, so I would have this:

CodePudding user response：

You could do it like this using a subquery https://prestodb.io/docs/current/sql/select.html

select PROJECT, APPROVAL_DATE, 
(
    select COUNT(*) 
    from TABLE2
    where TABLE1.PROJECT = TABLE2.PROJECT 
    and TABLE2.TEST_DATE <= TABLE1.APPROVAL_DATE 
) TOTAL_TESTS_BEFORE_APPROVAL_DATE 
FROM TABLE1

CodePudding user response：

here is how you can do it using left join :

select t1.project, t1.APPROVAL_DATE, count(t2.test_date) TOTAL_TESTS_BEFORE_APPROVAL_DATE
from table1 t1
left join table2 t2 
   on t1.project = t2.project
   and t1.APPROVAL_DATE > t2.TEST_DATE
group by t1.project, t1.APPROVAL_DATE