Why does SQL not allow the WITH clause for multiple subqueries?

Question

Why does SQL only allow nested subqueries?

For example, take this Question

• Find the users in each occupation with the highest number of ratings.

Table name is ratings, with columns

1. user_id
2. occupation
3. rating

In Postgres or Bigquery, I would do

with ratings_by_user as (
select occupation, user_id, count(*) num_ratings
from ratings
group by 1,2
),

max_ratings_by_occupation as (
select occupation, max(num_ratings) as max_ratings
from ratings_by_user
group by 1
),

select occupation, user_id
from ratings_by_user
inner join max_ratings_by_occupation
using (occupation)
where num_ratings = max_ratings

But I am not sure how to do this in SQL where I need to nest all the subqueries in one block. This is my attempt in SQL but it doesn't work.

select occupation, user_id, count(*) as num_ratings
from ( 
    select occupation, max(num_ratings) max_ratings 
    from ( 
        select occupation, user_id, count(*) num_ratings
        from users
        group by 1,2
        ) as ratings_table
    group by 1
    ) as max_ratings_table
)
inner join ratings on ratings.occupation = max_ratings_table.occupation
where max_ratings = num_ratings

Can anyone enlighten me on how I use the same style Postgres / Bigquery in SQL where I wish to handle my subqueries sequentially? I just find it hard to solve complex questions in one big chunk.

Thank you so much for your time.

Answer 1

Per https://dev.mysql.com/doc/refman/8.0/en/with.html and MySQL "WITH" clause - WITH is only supported on MySQL 8+. Ensure you're using an appropriate version of MySQL

COnverting to a nested version isn't hard. We take your working sql:

with ratings_by_user as (
select occupation, user_id, count(*) num_ratings
from ratings
group by 1,2
),

max_ratings_by_occupation as (
select occupation, max(num_ratings) as max_ratings
from ratings_by_user
group by 1
),

select occupation, user_id
from ratings_by_user
inner join max_ratings_by_occupation
using (occupation)
where num_ratings = max_ratings

we copy everything including the brackets of the WITH, and paste it in before the use of the alias.

step 1, cut the ratings_by_user and paste it in everwhere the ratings_by_user is used (twice)

--cut from here
with ratings_by_user as ,

max_ratings_by_occupation as (
select occupation, max(num_ratings) as max_ratings
from 
  --paste to here
  (
    select occupation, user_id, count(*) num_ratings
    from ratings
    group by 1,2
  ) ratings_by_user
group by 1
),

select occupation, user_id
from
--and also paste to here 
(
  select occupation, user_id, count(*) num_ratings
  from ratings
  group by 1,2
) ratings_by_user
inner join max_ratings_by_occupation
using (occupation)
where num_ratings = max_ratings

step 2, cut the max_ratings_by_occupation in and paste it in where it's used:

with ratings_by_user as ,

--cut from here
max_ratings_by_occupation as ,

select occupation, user_id
from
(
  select occupation, user_id, count(*) num_ratings
  from ratings
  group by 1,2
) ratings_by_user
inner join 

--paste to here
(
  select occupation, max(num_ratings) as max_ratings
  from 
  (
    select occupation, user_id, count(*) num_ratings
    from ratings
    group by 1,2
  ) ratings_by_user
  group by 1
) max_ratings_by_occupation

using (occupation)
where num_ratings = max_ratings

step 3, clean up the empty withs

select occupation, user_id
from
(
  select occupation, user_id, count(*) num_ratings
  from ratings
  group by 1,2
) ratings_by_user
inner join 
(
  select occupation, max(num_ratings) as max_ratings
  from 
  (
    select occupation, user_id, count(*) num_ratings
    from ratings
    group by 1,2
  ) ratings_by_user
  group by 1
) max_ratings_by_occupation

using (occupation)
where num_ratings = max_ratings

This would be a start for optimizing/rewriting. The tricky part for this was that it uses ratings_by_user twice, so two pastes were needed in step 1

Your reformat attempt didn't work out because you were trying to use, at an outer level, a resultset that only existed at an inner level:

select occupation, user_id, count(*) as num_ratings
from 

( --max_ratings_table available inside these brackets
    select occupation, max(num_ratings) max_ratings 
    from ( 
        select occupation, user_id, count(*) num_ratings
        from users
        group by 1,2
        ) as ratings_table
    group by 1
    ) as max_ratings_table
  --end of max_ratings_table availability
) 

inner join ratings on ratings.occupation = max_ratings_table.occupation
--                                         ^^^^^^^^^^^^^^^^^
--                                       mrt not available here
where max_ratings = num_ratings