# No acceleration for queries with "SQL WITH" clause (sub-query refactoring)

**URL:** <https://community.dremio.com/t/no-acceleration-for-queries-with-sql-with-clause-sub-query-refactoring/3321>\
**Category:** Uncategorized\
**Created:** [May 8, 2019, 10:07am UTC](https://community.dremio.com/t/no-acceleration-for-queries-with-sql-with-clause-sub-query-refactoring/3321 "2019-05-08T10:07:35Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![joejk](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/joejk/32/1446_2.png) [@joejk](https://community.dremio.com/u/joejk)\
**Post date:** [May 8, 2019, 10:07am UTC](https://community.dremio.com/t/no-acceleration-for-queries-with-sql-with-clause-sub-query-refactoring/3321/1 "2019-05-08T10:07:35Z")

</div>

In simplified terms, the following query gets accelerated  
`select * from A`  
But if I group on `select * from A` as a subquery, it doesn’t get accelerated.  
E.g.  
`select Col1, count(1) from (select * from A) group by Col1`

Raw and Aggregate reflections are available for the tables referred. I think the execution plan is vastly different for both the cases. If there is a specific way to write this query so as to force Dremio to pick up the reflection, it would be great to know. Else I have to download the data from the non-grouped query and use external product like Redash Cache or Excel to pivot on it. That is a bit cumbersome to ask of my team.

Below is the original query which gets accelerated:

```auto
    with dau as (
        select distinct
            user_profile_id
            , answered_date
        from core.answers a
        where
            answered_date <= timestampadd(day,-2,current_date)
            and answered_date >= timestampadd(day,-6,current_date)
    )
    , signups as (
        select
            signups.*
        from "core".signups
        where signups.user_profile_id in (select user_profile_id from dau)
    )
     , subscribers as (
            select *
            from 
                "core".subscriptions
            where 
                subscriptions.user_profile_id in (select user_profile_id from dau)
                and module_id in (1,2,6)
    )
    , base as (
            select
                dau.*
                , case    
                    when signups."klass" in ('5','6') then '5-6'
                    when signups."klass" in ('7','8') then '7-8'
                    when signups."klass" in ('9','10') then '9-10'
                    else '11/12/12+'
                end as klass
                , case 
                    when subscribers.user_profile_id is null then 'free'
                    else 'paid'
                end as subscription
            from dau 
                inner join signups on dau.user_profile_id = signups.user_profile_id
                left join subscribers on subscribers.user_profile_id = dau.user_profile_id
    )
    select * from base

```

In the above query, if I replace the last line (`select * from base`) with the one below, it won’t be accelerated.

```auto
    , base2 as (select * from base)
    select 
      answered_date
      , klass, subscription
      , count(distinct user_profile_id) as dau 
    from base2 
    group by answered_date, klass, subscription

```

The query plan is vastly different in both of them.  
Query profile for the non-grouped & accelerated [04d0d19e-6f46-45e1-b984-191e0829d45a.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/f/faa2f41e834161b7565f8d615d3380860b7893f4.zip) (56.3 KB)

Query profile for the grouped & non-accelerated  
[0709016b-f280-458c-974a-0884243ad07b.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/c/cf0ba1be3592bf448262d2ec6b088891a9f90e7b.zip) (48.6 KB)