Advanced SQL Tutorial: Data Format Practice Question

Hi all,

I was wondering why my query (below) does not return the same result as the solution to the  "SQL Date Format Practice Problem" despite, by my reckoning, being logically equivalent. 

SELECT
  companies.category_code,
  COUNT((CASE
    WHEN (acquisitions.acquired_at_cleaned - companies.founded_at_clean::timestamp) <= INTERVAL '3 years' THEN 1 ELSE NULL END)) AS "to_3",
  COUNT((CASE
    WHEN (acquisitions.acquired_at_cleaned - companies.founded_at_clean::timestamp) <= INTERVAL '5 years' THEN 1 ELSE NULL END)) AS "to_5",
  COUNT((CASE
    WHEN (acquisitions.acquired_at_cleaned - companies.founded_at_clean::timestamp) <= INTERVAL '10 years' THEN 1 ELSE NULL END)) AS "to_10",
  COUNT(1) AS "total_count"

  FROM tutorial.crunchbase_companies_clean_date companies
  JOIN tutorial.crunchbase_acquisitions_clean_date acquisitions
    ON acquisitions.company_permalink = companies.permalink
 WHERE founded_at_clean IS NOT NULL
 GROUP BY 1
 ORDER BY 5 DESC

Everything is exactly same except for the way the CASE WHEN statements are formulated(i.e., I simply move the founded date to the left side of the inequality.)

Any help would be most appreciated!

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hey Ravi Dayabhai ! After comparing the outputs, I don't see a difference between the output of the tutorial's solution and the slightly modified one above.

    Check out this report, which contains both outputs side-by-side. I don't see a discrepancy between the two. Let me know if I'm missing it!

    Reply Like
  • Nate , 😅 I swear it was showing something different. Anywho, thanks for confirming I am losing it...just not for the reasons I suspected! 

    Reply Like
  • 😂Happens to the best, Ravi !

    Reply Like
Like Follow
  • Status Answered
  • 11 days agoLast active
  • 3Replies
  • 217Views
  • 2 Following

Create A New Discussion

Share your thoughts