Domain 1EasyCORE

Unique Orders and Customers Per Month

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.

Domain: Easy•Updated: 2026-08-31
#Algorithms#LeetCode#qwen2.5-coder:14b#**FAANG asked:** 0

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 Orders with 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_id values should be considered only once.
  • Duplicate customer_id values 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

  1. Extract Month from Order Date: Use a date function to extract the year and month from order_date and format it as ‘YYYY-MM’.
  2. Group By Month: Group the results by the extracted month.
  3. Count Unique Orders and Customers: For each group, count the distinct order_id values for unique orders and distinct customer_id values for unique customers.
  4. 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 Orders table. 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 Orders table is empty, the function will return an empty DataFrame.
  • Duplicate Orders: The use of nunique ensures that duplicate order_id values are counted only once per month.
  • Duplicate Customers: Similarly, duplicate customer_id values 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.