Code Best Practices Checker | SQL

SQL Code Refactoring

This code snippet is a refactoring of an SQL query that creates a table called "new_customers_each_month". It selects the year, month, and count of distinct customer IDs from a sales table. The refactored code improves performance by using the YEAR a...


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

The given code snippet appears to be written in SQL.

Refactored code:

CREATE TABLE new_customers_each_month AS
SELECT YEAR(order_date) AS year,
       MONTH(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 YEAR(order_date) > (SELECT MIN(YEAR(order_date)) FROM sales)
)
GROUP BY year, month;

Improvements:

  • Removed the unnecessary usage of EXTRACT function and replaced it with YEAR and MONTH functions.
  • Refined the subquery by removing the use of EXTRACT and replaced it with YEAR.
  • MIN(EXTRACT(YEAR FROM order_date)) has been simplified to MIN(YEAR(order_date)).
  • Enhanced the readability of the query by aligning the SELECT, FROM, WHERE, and GROUP BY clauses.
  • Ensured the core functionality remains intact.

By applying these changes, we have improved the code's performance, readability, and adherence to SQL best practices.

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 Code Best Practices Checkers
Apache Flink Code Best Practices CheckerApache Pig Code Best Practices CheckerAzure Data Factory Code Best Practices CheckerC/C++ Code Best Practices CheckerCouchDB Code Best Practices CheckerDAX Code Best Practices CheckerExcel Code Best Practices CheckerFirebase Code Best Practices CheckerGoogle BigQuery Code Best Practices CheckerGoogle Sheets Code Best Practices CheckerGraphQL Code Best Practices CheckerHive Code Best Practices CheckerJava Code Best Practices CheckerJavaScript Code Best Practices CheckerJulia Code Best Practices CheckerLua Code Best Practices CheckerM (Power Query) Code Best Practices CheckerMATLAB Code Best Practices CheckerMongoDB Code Best Practices CheckerOracle Code Best Practices CheckerPostgreSQL Code Best Practices CheckerPower BI Code Best Practices CheckerPython Code Best Practices CheckerR Code Best Practices CheckerRedis Code Best Practices CheckerRegex Code Best Practices CheckerRuby Code Best Practices CheckerSAS Code Best Practices CheckerScala Code Best Practices CheckerShell Code Best Practices CheckerSPSS Code Best Practices CheckerSQL Code Best Practices CheckerSQLite Code Best Practices CheckerStata Code Best Practices CheckerTableau Code Best Practices CheckerVBA Code Best Practices Checker