Split date range into weeks in sql

Given a table called Project, I need the list of team_id’s who won at least an award every week in last 3 months

launch_date team_id project_name
2019-01-01   123      A
2019-01-01   345      B
2019-01-01   357      C
2019-01-09   123      D
2019-01-08   345      E
2019-01-21   123      F

project_name    award
A                Y
B                N
C                Y
D                Y
E                N
F                Y

last 3 months can be achieved with below where condition but how do i split the launch_date into weekly intervals

where launch_date >= sysdate - 90

With the given data, answer should be team id 123

Answer

In your sample data, You have only given 21 days of data instead of 3 months.

You can find out the total number of weeks and their week starting date which can then be compared with your table data to check if an award is won by the team for each week as follows:

SQL> --SAMPLE DATA
SQL> with teams (launch_date, team_id, project_name)
  2  as
  3  (SELECT DATE'2019-01-01', 123, 'A' FROM DUAL UNION ALL
  4  SELECT DATE'2019-01-01', 345, 'B' FROM DUAL UNION ALL
  5  SELECT DATE'2019-01-01', 357, 'C' FROM DUAL UNION ALL
  6  SELECT DATE'2019-01-09', 123, 'D' FROM DUAL UNION ALL
  7  SELECT DATE'2019-01-08', 345, 'E' FROM DUAL UNION ALL
  8  SELECT DATE'2019-01-21', 123, 'F' FROM DUAL),
  9  AWARDS(project_name, award)
 10  AS
 11  (SELECT 'A','Y' FROM DUAL UNION ALL
 12  SELECT 'B','N' FROM DUAL UNION ALL
 13  SELECT 'C','Y' FROM DUAL UNION ALL
 14  SELECT 'D','Y' FROM DUAL UNION ALL
 15  SELECT 'E','N' FROM DUAL UNION ALL
 16  SELECT 'F','Y' FROM DUAL),
 17  -- YOUR QUERY START FROM HERE
 18  -- WITH
 19  WKS(DT) AS
 20  (SELECT DISTINCT TRUNC(DATE '2019-01-21' - LEVEL + 1, 'W')
 21  FROM DUAL CONNECT BY LEVEL <= 21
 22  )
 23  SELECT T.TEAM_ID
 24    FROM WKS W
 25    LEFT JOIN TEAMS T ON W.DT = TRUNC(T.LAUNCH_DATE, 'W')
 26    LEFT JOIN AWARDS A ON A.PROJECT_NAME = T.PROJECT_NAME
 27  WHERE A.AWARD = 'Y'
 28  GROUP BY T.TEAM_ID
 29  HAVING COUNT(1) = ( SELECT COUNT(1) FROM WKS);

   TEAM_ID
----------
       123

SQL>

In WKS cte for 3 months data, You need to replace the

WKS(DT) AS
 (SELECT DISTINCT TRUNC(DATE '2019-01-21' - LEVEL + 1, 'W')
  FROM DUAL CONNECT BY LEVEL <= 21
 )

with

WKS(DT) AS
( SELECT DISTINCT TRUNC(sysdate - LEVEL + 1, 'W')
 FROM DUAL CONNECT BY LEVEL <= trunc(sysdate) - add_months(trunc(sysdate), -3 
)