Analyzing Customer Order Frequency with Correlated Subqueries

This challenge focuses on utilizing correlated subqueries in SQL to analyze customer order frequency. Understanding order frequency can be valuable for targeted marketing campaigns, identifying loyal customers, and detecting potential churn. You'll be tasked with writing SQL queries that leverage correlated subqueries to determine the number of orders placed by each customer within a specific timeframe.

Problem Description

You are given two tables: Customers and Orders. The Customers table contains information about customers, and the Orders table contains information about their orders. Your goal is to write SQL queries that, for each customer, determine the number of orders they placed within the last 30 days. The query must use a correlated subquery to achieve this.

Tables:

• Customers:
  • customer_id (INT, Primary Key) - Unique identifier for each customer.
  • customer_name (VARCHAR) - Name of the customer.
• Orders:
  • order_id (INT, Primary Key) - Unique identifier for each order.
  • customer_id (INT, Foreign Key referencing Customers.customer_id) - The customer who placed the order.
  • order_date (DATE) - The date the order was placed.

Requirements:

• The query must return a result set with two columns: customer_name and order_count.
• order_count represents the number of orders placed by each customer within the last 30 days (including today).
• The query must use a correlated subquery. Using a simple JOIN or window function is not acceptable for this challenge.
• The query should handle cases where a customer has no orders in the last 30 days (the order_count should be 0 in this case).

Expected Behavior:

The query should accurately count the number of orders for each customer within the specified timeframe. The results should be ordered alphabetically by customer_name.

Edge Cases to Consider:

• Customers with no orders at all.
• Customers with orders only outside the 30-day window.
• Empty Orders table.
• Empty Customers table.

Examples

Example 1:

Customers:
customer_id | customer_name
------------|---------------
1           | Alice
2           | Bob
3           | Charlie

Orders:
order_id | customer_id | order_date
---------|-------------|------------
1        | 1           | 2024-01-15
2        | 2           | 2024-01-20
3        | 1           | 2024-02-01
4        | 3           | 2024-02-05
5        | 1           | 2023-12-25

(Assuming today is 2024-02-05)

Output:
customer_name | order_count
---------------|-------------
Alice         | 2
Bob           | 1
Charlie       | 1

Explanation: Alice placed 2 orders (order_id 3 and 1) within the last 30 days. Bob placed 1 order (order_id 2) within the last 30 days. Charlie placed 1 order (order_id 4) within the last 30 days.

Example 2:

Customers:
customer_id | customer_name
------------|---------------
1           | Alice
2           | Bob

Orders:
order_id | customer_id | order_date
---------|-------------|------------
1        | 1           | 2023-12-20
2        | 2           | 2023-11-15

(Assuming today is 2024-02-05)

Output:
customer_name | order_count
---------------|-------------
Alice         | 0
Bob           | 0

Explanation: Neither Alice nor Bob placed any orders within the last 30 days.

Example 3:

Customers:
customer_id | customer_name
------------|---------------
1           | Alice

Orders:
(Empty Orders table)

(Assuming today is 2024-02-05)

Output:
customer_name | order_count
---------------|-------------
Alice         | 0

Explanation: Alice exists, but has no orders.

Constraints

• The order_date column is of type DATE.
• The database system is assumed to be standard SQL compliant (e.g., PostgreSQL, MySQL, SQL Server).
• The query should be reasonably efficient. While optimization is not the primary focus, avoid excessively inefficient approaches.
• The number of rows in the Customers table will be less than 1000.
• The number of rows in the Orders table will be less than 10000.

Notes

• Remember that a correlated subquery references columns from the outer query. This is crucial for this challenge.
• Consider using the DATE() function (or equivalent in your specific SQL dialect) to compare dates accurately.
• Think about how to handle customers who have never placed any orders. A LEFT JOIN might be helpful, but the core logic must be a correlated subquery.
• The 30-day window is calculated from the current date (today). You may need to use a function like CURRENT_DATE or GETDATE() to represent the current date.