Problem in writing query in sql with subtracting two dates columns

OneLearn Course SQL
,
#1
  1. Write a query that counts the number of companies acquired within 3 years, 5 years, and 10 years of being founded (in 3 separate columns). Include a column for total companies acquired as well. Group by category and limit to only rows with a founding date. (USE CRUNCHBASE Dataset)

for the question above i am not able to get 3 years ,5,years or 10 years as difference between acquired_at_cleaned and founded_at_clean, should i use subquery here and also how to get difference of 3 ,5 or 10 years using two tables?

#2

Whenever you have to solve a question like this you should think step wise.
First step needs to be joining the two tables

crunchbase_companies_clean_date and crunchbase_acquisitions_clean_date

so that you can get acquired_at_cleaned and founded_at_clean column in one single table.

Then as a second step, i would suggest you to think of the query to find all the companies acquired within 3 year (don’t focus on 5 or 10 year problem for now). Try to think of how to achieve it and put your answer below and then we will move forward. (hint - you need to subtract the acquisition and founding date)

#3

I am able to subtract but not getting how to filter for 3 years after subtraction?

#4

In the lecture on advanced SQL we subtracted two timestamps and then compared it with INTERVAL

In the same way, the answer can be found can be shown below

SELECT companies.category_code,
     COUNT(CASE when acquisitions.acquired_at_cleaned::timestamp - companies.founded_at_clean::timestamp < INTERVAL '3year' THEN 1 ELSE NULL END) AS 3_year_acquisitions 
FROM tutorial.crunchbase_companies_clean_date AS companies
JOIN tutorial.crunchbase_acquisitions_clean_date AS acquisitions
ON acquisitions.company_permalink = companies.permalink
 WHERE companies.founded_at_clean IS NOT NULL
 GROUP BY 1

We have done groupby and filtering as mentioned in the question.