Documentation Generator | SQL

Create Table for Monthly Count of New Customers

This SQL code snippet creates a new table called "new_customers_each_month" to store the monthly count of new customers. It selects the year, month, and count of distinct customer IDs from the "sales" table, filtering out customer IDs that have alrea...


Empty image or helper icon

This Query related with thread "Creating a Table to Track New Customers Each Month"

Prompt

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;

Answer

-- CREATE TABLE new_customers_each_month AS
-- This code snippet creates a new table called "new_customers_each_month" to store the monthly count of new customers.

-- SELECT EXTRACT(YEAR FROM order_date) AS year, EXTRACT(MONTH FROM order_date) AS month, COUNT(DISTINCT customer_id) AS new_customers
-- This code snippet selects the year, month, and count of distinct customer_id(s) from the "sales" table.
-- The count represents the number of new customers for each month.

-- FROM sales
-- This code snippet specifies the source table to retrieve the data from, in this case, the "sales" table.

-- 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)
-- )
-- This code snippet filters out the customer_id(s) that have already made a purchase in a previous year.
-- The subquery retrieves the minimum year from the "sales" table, and the WHERE clause filters customer_id(s) where
-- the order_date year is greater than the minimum year extracted.

-- GROUP BY year, month;
-- This code snippet groups the result set by year and month, allowing us to calculate the count of new customers for each month.

Create your Thread using our flexible tools, share it with friends and colleagues.

Your current query will become the main foundation for the thread, which you can expand with other tools presented on our platform. We will help you choose tools so that your thread is structured and logically built.

Description

More Documentation Generators
Apache Flink Documentation GeneratorApache Pig Documentation GeneratorAzure Data Factory Documentation GeneratorC/C++ Documentation GeneratorCouchDB Documentation GeneratorDAX Documentation GeneratorExcel Documentation GeneratorFirebase Documentation GeneratorGoogle BigQuery Documentation GeneratorGoogle Sheets Documentation GeneratorGraphQL Documentation GeneratorHive Documentation GeneratorJava Documentation GeneratorJavaScript Documentation GeneratorJulia Documentation GeneratorLua Documentation GeneratorM (Power Query) Documentation GeneratorMATLAB Documentation GeneratorMongoDB Documentation GeneratorOracle Documentation GeneratorPostgreSQL Documentation GeneratorPower BI Documentation GeneratorPython Documentation GeneratorR Documentation GeneratorRedis Documentation GeneratorRegex Documentation GeneratorRuby Documentation GeneratorSAS Documentation GeneratorScala Documentation GeneratorShell Documentation GeneratorSPSS Documentation GeneratorSQL Documentation GeneratorSQLite Documentation GeneratorStata Documentation GeneratorTableau Documentation GeneratorVBA Documentation Generator