Oracle SQL - Running a query multiple times

Question

I have this query:

select '2012-Nov-01' As "Week_Of",
count(player_id) as cohort_size ,
count(case 
    when trunc(init_dtime)-trunc(create_dtime) >= 0 
    then player_id 
  end) as Day_0_Ret,
count(case 
    when trunc(init_dtime)-trunc(create_dtime) >= 1 
    then player_id 
  end) as Day_1_Ret,
count(case 
    when trunc(init_dtime)-trunc(create_dtime) >= 2
    then player_id 
  end) as Day_2_Ret,
count(case 
    when trunc(init_dtime)-trunc(create_dtime) >= 3
    then player_id 
  end) as Day_3_Ret
from player
where trunc(create_dtime) > To_Date('2012-Nov-01','yyyy-mon-dd')-7
and trunc(create_dtime) <= To_Date('2012-Nov-01','yyyy-mon-dd')
and world_flag != '1'
Which outputs:

Week_Of       Cohort_size    Day_0_Ret    Day_1 Ret    Day_2_Ret    Day_3_Ret
2012-Nov-01    2426           2426        1412         1349          1316

I want to be able to run this query for multiple sets of dates, so that my output would look something like:

Week_Of       Cohort_size    Day_0_Ret    Day_1 Ret    Day_2_Ret    Day_3_Ret
2012-Nov-01    2426           2426        1412         1349          1316
2012-Nov-08    5106           2458        1945         1379          1248
2012-Nov-15    3580           1476        1412         1349          1146

Instead of having the re run the query everytime for a specific week of. Is this possible?

If you're willing to manually enter these dates then you can UNION then together and then INNER JOIN the result with your query. — PM 77-1
– PM 77-1, Commented Mar 6, 2013 at 23:25

Gordon Linoff · Accepted Answer · 2013-03-07 00:29:23Z

4

The following query changes your logic to a group by rather than a where:

select to_char(max(create_dtime), 'yyyy-mm-dd') As "Week_Of",
       count(player_id) as cohort_size ,
count(case 
    when trunc(init_dtime)-trunc(create_dtime) >= 0 
    then player_id 
  end) as Day_0_Ret,
count(case 
    when trunc(init_dtime)-trunc(create_dtime) >= 1 
    then player_id 
  end) as Day_1_Ret,
count(case 
    when trunc(init_dtime)-trunc(create_dtime) >= 2
    then player_id 
  end) as Day_2_Ret,
count(case 
    when trunc(init_dtime)-trunc(create_dtime) >= 3
    then player_id 
  end) as Day_3_Ret
from player
where trunc(create_dtime) >= To_Date('2012-Nov-01','yyyy-mon-dd') - 6
and world_flag != '1'
group by trunc((trunc(create_dtime) - (To_Date('2012-Nov-01','yyyy-mon-dd') -  6))/7)
order by 1

It chooses the last available date to describe the week....

edited Mar 7, 2013 at 0:29

answered Mar 6, 2013 at 23:26

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Americo Over a year ago

This doesn't seem to work 1 for 1. I'm not exactly sure what's missing but, when I run this , the week of 2012-Nov-01 out put day_0_ret of 2784 instead of 2426, and so on down the line.

Gordon Linoff Over a year ago

@Stuave . . . There was an "off-by-one" error on the weeks. I consider "week-of" to be the beginning date, rather than the end date. I noticed that your original query subtracts 7 days before Nov 1, rather than adds them. I fixed the query.

Americo Over a year ago

@GordonLinoff I apologize as I'm not quite sure I follow. I was hoping to get your query to tie out to my original query for the "Week of Nov 1" as I had defined it. I'm not sure I follow the subtraction of 6 at the bottom. Is there anyway to suggest a way to tie out to my original query? Thank you!

Gordon Linoff Over a year ago

@stuave . . . >= To_Date('2012-Nov-01','yyyy-mon-dd') - 6 is the same as > To_Date('2012-Nov-01','yyyy-mon-dd') - 7. They should be counting the weeks from 6 days before that date.

Americo Over a year ago

@GordonLinoff perhaps it is the group by trunc((trunc(create_dtime) - To_Date('2012-Nov-01','yyyy-mon-dd') - 6)/7)? because when i run the query instead of week of (or week end as it should be titled) 2012-11-01, it becomes 2012-10-31.

|

Collectives™ on Stack Overflow

Oracle SQL - Running a query multiple times

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related