Search code examples
sqloracleoracle12c

Total minutes streamed within 60 days from launch?

I need to find the total minutes streamed per title within first 60 days from launch date:

xx table:

title_id integer
minutes_streamed float
streamstartdate date (yyyymmdd hh:ss)

yy table:

genre varchar
launch_date date (yyyymmdd hh:ss)

SELECT xx.title_id, yy.genre, SUM(xx.minutes_streamed) AS total_minutes
  FROM xx
  JOIN yy
    ON xx.titleid = yy.titleid
 WHERE xx.streamstartdate BETWEEN yy.launch_date + 60 
   AND yy.launch_date
 GROUP BY 1, 2

Is this the correct approach to take/any other, more 'elegant' ways to do this?

Solution

  • Your method is fine, but this logic is off:

    WHERE xx.streamstartdate between yy.launch_date+60 and yy.launch_date

    The limits for between must be and . So:

    WHERE xx.streamstartdate between yy.laucn_date and yy.launch_date+60