Creating a Table to Track New Customers Each Month

To create a table that shows brand new customers each month, we can use the following SQL query:

CREATE TABLE new_customers_each_month AS
SELECT EXTRACT(YEAR FROM order_date) AS year,
       EXTRACT(MONTH FROM order_date) AS month,
       COUNT(DISTINCT customer_id) AS new_customers
FROM sales
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id
    FROM sales
    WHERE EXTRACT(YEAR FROM order_date) > (SELECT MIN(EXTRACT(YEAR FROM order_date)) FROM sales)
)
GROUP BY year, month;

Explanation:

• The CREATE TABLE statement is used to create a new table named new_customers_each_month.
• The SELECT statement retrieves the required data:
  • EXTRACT(YEAR FROM order_date) and EXTRACT(MONTH FROM order_date) are used to extract the year and month from the order_date column.
  • COUNT(DISTINCT customer_id) counts the number of distinct customer IDs.
• The WHERE clause filters the customers who placed their first order in the current year:
  • The subquery (SELECT MIN(EXTRACT(YEAR FROM order_date)) FROM sales) retrieves the earliest year from the sales data.
  • customer_id NOT IN (...) excludes customers who placed an order in any year after the earliest year.
• Finally, the GROUP BY clause groups the data by year and month.

Note: The SQL query assumes that the original sales table is named sales. Make sure to replace it with the actual name of your sales table.