Have to get the number of companies that were acquired within a period of 3 years from foundation
Any suggestions for a better code for the same are most welcome!
My code
COUNT( CASE WHEN (acquisitions.acquired_at_cleaned - companies.founded_at_clean::timestamptz) <= (INTERVAL '3 Year') THEN 1
ELSE NULL END )as acquired_3_yrs
Website answer
COUNT(CASE WHEN acquisitions.acquired_at_cleaned <= companies.founded_at_clean::timestamp + INTERVAL '3 years'THEN
1 ELSE NULL END) AS acquired_3_yrs
These both are giving different outputs, the variation is very small. Kindly explain the difference where did I go wrong.
Your problem is that you are comparing between different units of time. If you simply select acquisitions.acquired_at_cleaned - companies.founded_at_clean, you will get an answer expressed as so many days and hours. This cannot be accurately compared with an interval expressed in years.
As a test try simply
SELECT (INTERVAL '1080 DAY') <= (INTERVAL '3 YEAR');
and
SELECT (INTERVAL '1081 DAY') <= (INTERVAL '3 YEAR');
The first is true, but the second is false. Why? Because there are 12 months in a year. And how many days in a month? Effectively Postgres uses a year of 12 months of 30 days. This is not a bug. There is no really correct way to do it otherwise (think about leap years!).
The Website answer works correctly because 3 years is added to a specific date, and that will give the expected answer,