1565. Unique Orders and Customers Per Month
Model: qwen2.5-coder:14b
Topics:
FAANG asked: 0
Acceptance rate: 83.1%
Problem Breakdown
The problem requires us to determine the number of unique orders and customers per month from a given dataset. The input is a table containing order details with columns such as order_id, customer_id, and order_date. The output should be a list of months along with the count of unique orders and unique customers for each month.
Inputs:
- A table named
Orderswith at least the following columns:order_id: Unique identifier for each order.customer_id: Identifier for the customer who placed the order.order_date: Date when the order was placed.
Outputs:
- A result set with three columns:
month: The month in which the orders were placed (formatted as ‘YYYY-MM’).unique_orders_count: The count of unique orders in that month.unique_customers_count: The count of unique customers who placed orders in that month.
Implicit Constraints/Edge Cases:
- Duplicate
order_idvalues should be considered only once. - Duplicate
customer_idvalues should be considered only once per month. - If there are no orders for a particular month, the result should not include that month.
- The input table might be empty, resulting in an empty output.
Intuition
The brute-force approach would involve iterating through each row of the Orders table, grouping by month, and counting unique orders and customers. However, this approach would be inefficient with high time complexity due to repeated scans and aggregations.
A better approach is to use SQL’s aggregation functions along with date manipulation functions to efficiently group the data by month and count the distinct values for orders and customers. This leverages the database engine’s optimized query execution plan.
Approach
- Extract Month from Order Date: Use a date function to extract the year and month from
order_dateand format it as ‘YYYY-MM’. - Group By Month: Group the results by the extracted month.
- Count Unique Orders and Customers: For each group, count the distinct
order_idvalues for unique orders and distinctcustomer_idvalues for unique customers. - Order Results: Optionally, order the results by month to ensure a consistent output format.
Complexity Analysis
- Time Complexity: O(n log n), where n is the number of rows in the
Orderstable. The grouping operation typically involves sorting, which has a logarithmic complexity relative to the number of rows. - Space Complexity: O(n), as we need space to store the intermediate results of the grouped data.
Solution
import pandas as pd
def unique_orders_and_customers_per_month(orders: pd.DataFrame) -> pd.DataFrame:
# Extract month from order_date and format it as 'YYYY-MM'
orders['month'] = orders['order_date'].dt.to_period('M').astype(str)
# Group by month, count distinct orders and customers
result = orders.groupby('month') \
.agg(unique_orders_count=('order_id', 'nunique'),
unique_customers_count=('customer_id', 'nunique')) \
.reset_index()
return result
# Example usage:
# orders_df = pd.DataFrame({
# 'order_id': [1, 2, 3, 4],
# 'customer_id': [101, 102, 101, 103],
# 'order_date': ['2023-01-15', '2023-01-20', '2023-02-10', '2023-02-15']
# })
# print(unique_orders_and_customers_per_month(orders_df))
Edge Cases Handled
- Empty Input: If the
Orderstable is empty, the function will return an empty DataFrame. - Duplicate Orders: The use of
nuniqueensures that duplicateorder_idvalues are counted only once per month. - Duplicate Customers: Similarly, duplicate
customer_idvalues are counted only once per month. - No Orders in a Month: If no orders exist for a particular month, that month will not appear in the result set.