Domain 1EasyCORE

Customer Order Frequency

The problem requires us to determine the frequency of customer orders from a given dataset. The input consists of two tables: `Customers` and `Orders`. The `Customers` table contains customer information with columns `customer_id` and `name`, while the `Orders` table contains order details with columns `order_id`, `customer_id`, and `order_date`.

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

1511. Customer Order Frequency

Model: qwen2.5-coder:14b
Topics:
FAANG asked: 1
Acceptance rate: 74.1%


Problem Breakdown

The problem requires us to determine the frequency of customer orders from a given dataset. The input consists of two tables: Customers and Orders. The Customers table contains customer information with columns customer_id and name, while the Orders table contains order details with columns order_id, customer_id, and order_date.

The output should be a list of customers along with their order frequency, sorted in descending order by frequency. If two customers have the same frequency, they should be ordered alphabetically by name.

Implicit constraints include:

  • The Orders table may contain duplicate entries for the same customer.
  • The Customers table might not have corresponding entries in the Orders table (i.e., some customers might not have placed any orders).
  • The order_date field is irrelevant to this problem, so it can be ignored.

Intuition

A brute-force approach would involve iterating through each customer and counting their orders. However, this would result in a time complexity of O(n^2) where n is the number of customers, which is inefficient for large datasets.

The key insight is to leverage SQL’s aggregation capabilities to count the number of orders per customer efficiently. By grouping the Orders table by customer_id and counting the occurrences, we can obtain the desired frequency in a single pass through the data. This approach reduces the time complexity to O(n).

Approach

  1. Join Tables: Perform an inner join between the Customers and Orders tables on the customer_id field to associate each order with its corresponding customer.
  2. Group by Customer: Group the joined table by customer_id to aggregate orders per customer.
  3. Count Orders: Use the COUNT(*) function to count the number of orders for each customer.
  4. Order Results: Sort the results first by the order frequency in descending order and then by customer name in ascending order if frequencies are equal.

Complexity Analysis

  • Time Complexity: O(n log n), where n is the number of unique customers, due to the sorting step after counting orders.
  • Space Complexity: O(n) for storing the aggregated results.

Solution

import pandas as pd

def customer_order_frequency(customers: pd.DataFrame, orders: pd.DataFrame) -> pd.DataFrame:
    # Join tables on customer_id
    merged_df = customers.merge(orders, on='customer_id', how='left')
    
    # Group by customer_id and count orders
    order_counts = merged_df.groupby('customer_id').size().reset_index(name='order_count')
    
    # Sort by order frequency (descending) and name (ascending)
    result_df = order_counts.merge(customers[['customer_id', 'name']], on='customer_id')
    result_df.sort_values(by=['order_count', 'name'], ascending=[False, True], inplace=True)
    
    return result_df[['name', 'order_count']]

Edge Cases Handled

  • No Orders: Customers with no orders are handled by the left join, resulting in a count of 0 for these customers.
  • Duplicate Orders: The COUNT(*) function inherently handles duplicates by counting each occurrence.
  • Empty Tables: If either table is empty, the result will be an empty DataFrame, as expected.