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.

1511. Customer Order Frequency - Practice Online

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.