Skip to main content
Talentd

Customer Order Frequency - Solution & Explanation

EasyPremiumFree on FleetCodeDatabase5 min readAsked at: Amazon
Practice this problem

Problem Statement

Table: Customers

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| name          | varchar |
| country       | varchar |
+---------------+---------+
customer_id is the column with unique values for this table.
This table contains information about the customers in the company.

 

Table: Product

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| description   | varchar |
| price         | int     |
+---------------+---------+
product_id is the column with unique values for this table.
This table contains information on the products in the company.
price is the product cost.

 

Table: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| customer_id   | int     |
| product_id    | int     |
| order_date    | date    |
| quantity      | int     |
+---------------+---------+
order_id is the column with unique values for this table.
This table contains information on customer orders.
customer_id is the id of the customer who bought "quantity" products with id "product_id".
Order_date is the date in format ('YYYY-MM-DD') when the order was shipped.

 

Write a solution to report the customer_id and customer_name of customers who have spent at least $100 in each month of June and July 2020.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Customers table:
+--------------+-----------+-------------+
| customer_id  | name      | country     |
+--------------+-----------+-------------+
| 1            | Winston   | USA         |
| 2            | Jonathan  | Peru        |
| 3            | Moustafa  | Egypt       |
+--------------+-----------+-------------+
Product table:
+--------------+-------------+-------------+
| product_id   | description | price       |
+--------------+-------------+-------------+
| 10           | LC Phone    | 300         |
| 20           | LC T-Shirt  | 10          |
| 30           | LC Book     | 45          |
| 40           | LC Keychain | 2           |
+--------------+-------------+-------------+
Orders table:
+--------------+-------------+-------------+-------------+-----------+
| order_id     | customer_id | product_id  | order_date  | quantity  |
+--------------+-------------+-------------+-------------+-----------+
| 1            | 1           | 10          | 2020-06-10  | 1         |
| 2            | 1           | 20          | 2020-07-01  | 1         |
| 3            | 1           | 30          | 2020-07-08  | 2         |
| 4            | 2           | 10          | 2020-06-15  | 2         |
| 5            | 2           | 40          | 2020-07-01  | 10        |
| 6            | 3           | 20          | 2020-06-24  | 2         |
| 7            | 3           | 30          | 2020-06-25  | 2         |
| 9            | 3           | 30          | 2020-05-08  | 3         |
+--------------+-------------+-------------+-------------+-----------+
Output: 
+--------------+------------+
| customer_id  | name       |  
+--------------+------------+
| 1            | Winston    |
+--------------+------------+
Explanation: 
Winston spent $300 (300 * 1) in June and $100 ( 10 * 1 + 45 * 2) in July 2020.
Jonathan spent $600 (300 * 2) in June and $20 ( 2 * 10) in July 2020.
Moustafa spent $110 (10 * 2 + 45 * 2) in June and $0 in July 2020.

Approach Overview

Problem Overview: You need to identify customers who placed enough orders in two different months to meet a spending threshold. The database contains Customers, Orders, and Product tables. The task is to compute each customer's total spending for June and July and return customers whose spending in both months is at least $100.

Approach 1: Join + Group By + Having (O(n) time, O(k) space)

This approach joins the Orders table with Product to calculate the total order value using quantity * price. After the join, group rows by customer_id. Use conditional aggregation with SUM(CASE WHEN ...) to compute spending separately for June and July within the same grouped result. The HAVING clause filters customers whose monthly totals are both greater than or equal to 100. Time complexity is O(n) where n is the number of order records scanned, and space complexity is O(k) for grouped customer aggregates.

The key insight is computing both monthly totals in a single grouped query instead of running separate queries. Conditional aggregation allows you to scan the dataset once while building multiple metrics. This pattern appears frequently in database interview questions and analytics workloads.

Approach 2: Monthly Subqueries + Join (O(n) time, O(k) space)

Another way is to compute two separate subqueries: one that aggregates June spending per customer and another that aggregates July spending. Each subquery joins Orders and Product, groups by customer_id, and filters with HAVING SUM(quantity * price) >= 100. The two result sets are then joined on customer_id to keep customers that satisfy both conditions. Time complexity remains O(n) because all orders still need to be scanned, while space complexity is O(k) for intermediate grouped results.

This method is straightforward but requires multiple aggregations and an additional join. The query becomes longer and may perform slightly worse than a single-pass aggregation.

Recommended for interviews: The single-query solution using JOIN, GROUP BY, and conditional aggregation in HAVING is what most interviewers expect. It demonstrates strong SQL fundamentals and efficient data scanning. Understanding how to compute multiple metrics in one grouped query is a common pattern in SQL analytics problems and reporting pipelines. The subquery approach still works but mainly shows correctness rather than query optimization skill.

Solution

We can use the JOIN statement to join the Orders table and the Product table, and then join the result with the Customers table. We can filter out the records where the order_date is not in the year 2020, and then use the GROUP BY statement to group the data by customer_id. Finally, we can use the HAVING statement to filter out the customers whose spending in June and July is greater than or equal to 100.

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Join + Group By + Having (Conditional Aggregation)O(n)O(k)Best general solution. Computes multiple monthly totals in a single grouped scan.
Monthly Subqueries + JoinO(n)O(k)Useful when conditions are easier to express in separate aggregates.

Video Solution

LeetCode 1511 Amazon & Facebook Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 3,554 views views

Watch 3 more video solutions →

Frequently Asked Questions

Is Customer Order Frequency easy or hard?
Customer Order Frequency is classified as an Easy database problem with an acceptance rate around 67%. The difficulty mainly comes from understanding conditional aggregation and filtering grouped results with HAVING.
Customer Order Frequency Python/Java solution
This problem is designed for SQL rather than general programming languages. The expected solution is a MySQL query using JOIN, GROUP BY, and HAVING. Python or Java would only be used to execute the SQL query through a database connector.
How to solve Customer Order Frequency in O(n)?
Join Orders with Product to calculate each order's total price using quantity * price. Group rows by customer_id and compute monthly spending using conditional aggregation like SUM(CASE WHEN MONTH(order_date)=6 THEN quantity*price END). Use a HAVING clause to require both monthly totals to be at least 100. This single-pass aggregation keeps the complexity O(n).
What is the best approach for Customer Order Frequency?
The most efficient approach uses JOIN + GROUP BY + HAVING with conditional aggregation. Join Orders with Product to compute order value, group by customer_id, and calculate monthly totals using SUM with CASE conditions. Filter customers whose June and July totals are both at least 100. This scans the orders once and runs in O(n) time.
Is Customer Order Frequency asked at Google/Amazon/Meta?
SQL aggregation and reporting problems like this appear frequently in data-focused interviews at companies such as Amazon, Meta, and Google. The exact problem may vary, but conditional aggregation with GROUP BY is a common pattern used to test database fundamentals.
What data structure is used in Customer Order Frequency?
This problem relies on relational database operations rather than traditional data structures. The key concepts are SQL joins, grouped aggregation, and conditional sums. Internally, the database engine builds grouped aggregates keyed by customer_id.
What is the time complexity of Customer Order Frequency?
The SQL query typically runs in O(n) time where n is the number of rows in the Orders table. The database scans the orders, joins with Product to compute price values, and groups results by customer. Space complexity is O(k) for storing aggregated results for k customers.

Ready to solve this problem?

Practice Customer Order Frequency with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary