Documentation Generator | SQL

New Customers Count

Retrieves the count of new customers based on a given condition from the "Sales" table.


Empty image or helper icon

This Query related with thread ""

Prompt

SELECT COUNT(DISTINCT CustomerID) AS NewCustomers
FROM Sales
WHERE CustomerID IN (
    SELECT DISTINCT CustomerID
    FROM Sales
    WHERE Date <= (SELECT MAX(Date) FROM Sales)
        AND SalesAmount > 0
)

Answer

Documentation: New Customers Count

This query retrieves the count of new customers based on a given condition from the "Sales" table.

Signature

SELECT COUNT(DISTINCT CustomerID) AS NewCustomers
FROM Sales
WHERE CustomerID IN (
    SELECT DISTINCT CustomerID
    FROM Sales
    WHERE Date <= (SELECT MAX(Date) FROM Sales)
        AND SalesAmount > 0
)

Method

The SELECT statement is used to retrieve the count of distinct CustomerID values that meet the specified conditions. The result is labeled as NewCustomers.

Inputs

The query references the "Sales" table to perform the count calculation.

Outputs

The output is a single value representing the count of new customers that meet the specified condition.

Steps

  1. The innermost subquery retrieves the maximum date from the "Sales" table using the MAX() function.
  2. The second subquery retrieves CustomerID values that meet two conditions:
    • Date <= (SELECT MAX(Date) FROM Sales): This limits the results to records that have dates before or on the maximum date in the "Sales" table.
    • SalesAmount > 0: This ensures the records have a positive sales amount.
  3. The outermost query performs the distinct count on the CustomerID values returned by the previous subquery.

Example

Suppose the "Sales" table contains the following records:

CustomerID Date SalesAmount
1 2021-01-01 100
2 2021-01-02 200
3 2021-01-02 150
4 2021-01-03 -50
2 2021-01-03 300
5 2021-01-04 200

The query will return 3 as the count of new customers because:

  • CustomerID 2, 3, and 5 meet the condition of having a date before or on the maximum date in the "Sales" table (2021-01-04) and a positive sales amount.

Use Case

This query can be used to track and analyze the number of new customers acquired based on the specified conditions. The count of new customers can be a useful metric for measuring business growth and evaluating marketing strategies.

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

The "New Customers Count" query calculates the number of new customers by counting the distinct CustomerID values that meet the specified conditions. It considers customers whose sales amount is positive and have a date on or before the maximum date in the Sales table. This query can be used to track and analyze the number of newly acquired customers, providing insights into business growth and evaluating marketing strategies.

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