How do you write a SQL query to count the number of unique customers who made a purchase on a specific date from a sales transaction table that logs every item purchased?
To write a SQL query that counts the number of unique customers who made a purchase on a specific date from a sales transaction table, you will use the `SELECT`, `COUNT`, `DISTINCT`, `FROM`, and `WHERE` clauses. Assume your sales transaction table is named `sales_transactions` and it contains columns like `customer_id` to identify each unique customer and `transaction_date` to log the date of each purchase.
The SQL query to achieve this for a specific date, for example, '2023-10-26', is:
SELECT COUNT(DISTINCT customer_id) AS UniqueCustomersPurchased
FROM sales_transactions
WHERE transaction_date = '2023-10-26';
Let's break down each part of this query:
`SELECT COUNT(DISTINCT customer_id)`:
`SELECT` is the SQL keyword used to specify which data you want to retrieve from the database.
`COUNT()` is an aggregate function that calculates the number of rows that match a specified criterion. It counts non-null values within the parentheses.
`DISTINCT` is a keyword used inside the `COUNT()` function to ensure that only unique values are counted. In this context, it means that if a single customer made multiple purchases on '2023-10-26', they will only be counted once towards the total number of unique customers. Without `DISTINCT`, if a customer purchased five items on that date, they would contribute five to the count instead of one.
`customer_id` is the name of the column in the `sales_transactions` table that holds a unique identifier for each customer. This is the column whose unique values we want to count.
`AS UniqueCustomersPurchased`:
`AS` is an optional keyword used to assign a temporary name, or alias, to the resulting column in the query's output. This makes the output more readable and descriptive.
`UniqueCustomersPurchased` is the chosen alias for the computed count, clearly indicating what the number represents.
`FROM sales_transactions`:
`FROM` is the SQL keyword that specifies the table from which you want to retrieve the data.
`sales_transactions` is the name of the table that contains all the sales transaction records, including customer identifiers and transaction dates.
`WHERE transaction_date = '2023-10-26'`:
`WHERE` is a clause used to filter the rows based on a specified condition. Only the rows that satisfy this condition will be included in the calculation.
`transaction_date` is the name of the column in the `sales_transactions` table that stores the date of each purchase.
`= '2023-10-26'` is the condition. It specifies that only transactions that occurred on the exact date '2023-10-26' should be considered. The date format used here ('YYYY-MM-DD') should match the format in which dates are stored in your database.