title: how to solve given Correlated Subqueries is not supported within case when statement
body: <p>I have this code where I try to count every distinct user <strong>listed within 30 days prior rolling window</strong>, over the past 40 days.</p>
  
  <p>for example:  on the 12th feb (12/02) I need to count all listed user from 13th jan-12th feb (30 days) then on 11th feb I need to count from 12th jan to 11th feb and so on. I need to do this to a lot of other dates, is there a way to do it in presto? seeing as it does not support correlated subquery and when I try the code below, it returns 
  "Given correlated subquery is not supported"</p>
  
  <pre><code>WITH
  get_date as 
  (
    select 
    distinct date_trunc('day',date(uph.last_login)) as dates
    from user_profile_id_history uph 
    where uph.last_login &gt;= date('2020-02-04')-interval '30' day and uph.last_login &lt;= ( date'2020-02-04')
    order by 1 desc
  )
  
  
  select
   get_date.dates,
   (
     case when (dates &gt;= date('2020-02-04')-interval '30' day and dates &lt;= ( date'2020-02-04')) 
     then     
        (
          select
             count(distinct CASE WHEN date_trunc('month',date(up.registration_time)) &lt;= date_trunc('month',uph.last_login) THEN uph.userid END) 
           FROM
               user_profile_id_history uph
           LEFT JOIN
               user_profile up ON uph.userid = up.userid
           where uph.last_login &gt;= dates-interval '30' day and uph.last_login &lt;= dates
         ) end
    ) as mauser       
  from get_date
  group by 1`
  
  </code></pre>