how to solve the below CTE syntax error for the mentioned query

I have a sample query. I have tried to rewrite it with CTE but some errors occurred. Please check and solve the below query.

actual query;

select 
  dt.population, 
  dt.name, 
  c.name 
from 
  (
    select 
      distinct countrycode, 
      name 
    from 
      city
  ) as c, 
  lateral(
    select 
      name, 
      population 
    from 
      city 
    where 
      city.countrycode = c.countrycode 
    order by 
      population desc 
    limit 
      1
  ) as dt\G

with CTE

 with cte1  as (
    select 
      distinct countrycode, 
      name 
    from 
      city 
  ), 
     cte2  as (
    select 
      name, 
      population 
    from 
      city 
    where 
      cte2.countrycode = cte1.countrycode 
    order by 
      population desc 
    limit 
      1
  ) 
select 
  population, 
  name, 
  name 
from 
  ( select * 

  from cte1,cte2
  ) as a \G

error : ERROR 1060 (42S21): Duplicate column name 'name' ERROR 1054 (42S22): Unknown column 'cte2.countrycode' in 'where clause'

i have tried put alias for all the table but still am missing something.

Solution

Refer to this to know where you've define wrong:

with cte1  as (
   select 
     distinct countrycode, 
     name 
   from 
     city 
 ), 
    cte2  as (
   select 
     name, 
     population 
   from 
     city 
   where 
     cte2.countrycode = cte1.countrycode /*wrong*/
   order by 
     population desc 
   limit 
     1
 ) 
select 
 population, 
 name, /* wr    */
 name  /*   ong */
from 
 ( select * 

 from cte1,cte2
 ) as a \G /*wrong*/

This is a modification that is similar to what you've attempted - without error - but I'm pretty sure it's not what you intended:

 with cte1  as (
    select distinct countrycode, name 
    from city 
  ), 
     cte2  as (
    select name, population, countrycode
    from city 
    order by population desc 
    limit 1
  ) 
select 
  population, cte1.name, cte2.name 
from  cte1, cte2
    where 
      cte2.countrycode = cte1.countrycode;

Without sample data and expected result, I can only assume that this is what you actually want to do:

WITH cte1  AS (
    SELECT DISTINCT countrycode, NAME 
    FROM city 
  ), 
     cte2  AS (
    SELECT NAME, population, countrycode,
           ROW_NUMBER() OVER (PARTITION BY countrycode ORDER BY population DESC) AS Rn
    FROM city 
  )
SELECT 
  cte2.population, cte2.name, cte1.name
FROM  cte1
  JOIN cte2
    ON cte2.countrycode = cte1.countrycode
  WHERE Rn=1;

I've tested that with your original query (without cte) in this fiddle with some sample data and it does return the same result.