Showing Error code: 1060. Duplicate column name 'column_name', while running common table expression in MySQL

Question

There are 2 tables named customers and payment, when I am applying common table expression on two tables for generating temporary table 'temp' and searching for anything from the temporary table 'temp', it is showing 1060 error saying 'Duplicate column name 'customer_id' '. Query is as follows:

with temp as (
    select *, avg(amount) over(order by c.customer_id) as 'average price till this count',
    count(address_id) over(order by p.customer_id) as "count"
    from payment as p
    inner join customers as c
    on c.customer_id = p.customer_id
)
select concat(first_name, " ", last_name) as 'full name', amount
from temp;

column names in customers table are: customer_id, first_name, last_name, address_id

column names in payment table are: customer_id, amount, mode, payment_date.

Kindly suggest a solution and tell me if I am doing any mistake. Thanks

I have tried by removing extra query, still getting same error code 1060 Duplicate column name 'customer_id'

with temp as (
    select *
    from payment as p
    inner join customers as c
    on c.customer_id = p.customer_id
)
select first_name, last_name, amount
from temp;

Answer 1

I guess SELECT * what cause problem it select items from both tables

WITH temp AS (
  SELECT 
    c.customer_id AS c_customer_id, 
    p.customer_id AS p_customer_id, 
    -- include other columns you need here
    -- other part of query....;