Why do I get a duplicate column in the results of this query?

When I submit this query (from codeacademy course) I get two customer_id columns and can't understand why, and how do I remove the dupe column?

Screen cap of tables

WITH previous_results AS (
  SELECT customer_id, COUNT(customer_id) FROM orders
  GROUP BY customer_id
)
SELECT * FROM previous_results
JOIN customers
  ON previous_results.customer_id = customers.customer_id;

You are using SELECT * and Customer_ID exists in both your CTE "previous_results" and the customer table. SELECT * will return all columns from both. Specify your column names.

The reason why you're getting two customer_id columns in your query result is that you are selecting all columns from both previous_results and customers tables. Since both tables have a customer_id column, you end up with two columns with the same name.

To remove the duplicate customer_id column, you need to explicitly list the columns you want to select and exclude one of the customer_id columns. You can use the SQL AS keyword to rename the selected columns if needed.

Here's an updated version of your query that selects only the necessary columns and renames them to avoid duplicate column names:

WITH previous_results AS (
  SELECT customer_id, COUNT(customer_id) AS order_count
  FROM orders
  GROUP BY customer_id
)
SELECT customers.customer_id, customers.name, previous_results.order_count
FROM previous_results
JOIN customers ON previous_results.customer_id = customers.customer_id;

In this version, we only select the customer_id and name columns from the customers table, and the customer_id and order_count columns from the previous_results subquery. We also use the AS keyword to rename the COUNT(customer_id) column to order_count, which makes it easier to read the query result.