Problem in writing query in sql with subtracting two dates columns

  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?

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)

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

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

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