Is Find Loyal Customers easy or hard?

It is rated Medium with an acceptance rate of about 63.6%. The difficulty comes from translating the loyalty conditions into correct conditional aggregation and HAVING clauses, not from the algorithm itself. Once you are comfortable with GROUP BY and CASE expressions, the problem is straightforward.

Find Loyal Customers Python/Java solution

This problem is solved in SQL (MySQL) and Pandas on FleetCode, not in general-purpose Python or Java. In Pandas, group by customer_id, count purchase and refund masks with sum(), filter with the loyalty thresholds, and sort the output. Java would require loading rows into a HashMap, which is unnecessary here.

How to solve Find Loyal Customers in O(n)?

Group the transactions by customer_id once. Within each group, count purchases and refunds using conditional aggregation in the SELECT clause. Apply the loyalty thresholds in HAVING, then order the output as the problem requires. Avoid per-customer subqueries, since they re-scan the table and push the cost to O(n * m).

What is the best approach for Find Loyal Customers?

The best approach is a single GROUP BY on customer_id with conditional aggregation, using SUM(CASE WHEN ...) to count purchases and refunds, and HAVING to apply the loyalty condition. It scans the transaction table once, giving O(n) time where n is the number of rows.

Is Find Loyal Customers asked at Google/Amazon/Meta?

This problem is tagged General on FleetCode and does not have a confirmed company tag in our data. Its pattern, GROUP BY with conditional aggregation and HAVING, is common in SQL interviews at large tech companies, so practicing it helps regardless of which company you target.

What data structure is used in Find Loyal Customers?

The solution relies on a relational table with grouping and aggregation rather than a classic data structure. In Pandas, the equivalent is a DataFrame with groupby and Boolean masks. Conceptually, the grouping step builds a hash-like mapping from each customer_id to its aggregated counts.

What is the time complexity of Find Loyal Customers?

The optimal solution runs in O(n) time, where n is the number of transaction rows, because each row is read once during grouping. Space is O(k), where k is the number of distinct customers that appear in the grouped result.

Description Solution Video 7:27

3657. Find Loyal Customers

Medium63.6% acceptance

Table: customer_transactions

+------------------+---------+
| Column Name      | Type    | 
+------------------+---------+
| transaction_id   | int     |
| customer_id      | int     |
| transaction_date | date    |
| amount           | decimal |
| transaction_type | varchar |
+------------------+---------+
transaction_id is the unique identifier for this table.
transaction_type can be either 'purchase' or 'refund'.

Write a solution to find loyal customers. A customer is considered loyal if they meet ALL the following criteria:

Made at least 3 purchase transactions.
Have been active for at least 30 days.
Their refund rate is less than 20% .

Refund rate is the proportion of transactions that are refunds, calculated as the number of refund transactions divided by the total number of transactions (purchases plus refunds).

Return the result table ordered by customer_id in ascending order.

The result format is in the following example.

Example:

Input:

customer_transactions table:

+----------------+-------------+------------------+--------+------------------+
| transaction_id | customer_id | transaction_date | amount | transaction_type |
+----------------+-------------+------------------+--------+------------------+
| 1              | 101         | 2024-01-05       | 150.00 | purchase         |
| 2              | 101         | 2024-01-15       | 200.00 | purchase         |
| 3              | 101         | 2024-02-10       | 180.00 | purchase         |
| 4              | 101         | 2024-02-20       | 250.00 | purchase         |
| 5              | 102         | 2024-01-10       | 100.00 | purchase         |
| 6              | 102         | 2024-01-12       | 120.00 | purchase         |
| 7              | 102         | 2024-01-15       | 80.00  | refund           |
| 8              | 102         | 2024-01-18       | 90.00  | refund           |
| 9              | 102         | 2024-02-15       | 130.00 | purchase         |
| 10             | 103         | 2024-01-01       | 500.00 | purchase         |
| 11             | 103         | 2024-01-02       | 450.00 | purchase         |
| 12             | 103         | 2024-01-03       | 400.00 | purchase         |
| 13             | 104         | 2024-01-01       | 200.00 | purchase         |
| 14             | 104         | 2024-02-01       | 250.00 | purchase         |
| 15             | 104         | 2024-02-15       | 300.00 | purchase         |
| 16             | 104         | 2024-03-01       | 350.00 | purchase         |
| 17             | 104         | 2024-03-10       | 280.00 | purchase         |
| 18             | 104         | 2024-03-15       | 100.00 | refund           |
+----------------+-------------+------------------+--------+------------------+

Output:

+-------------+
| customer_id |
+-------------+
| 101         |
| 104         |
+-------------+

Explanation:

Customer 101:
- Purchase transactions: 4 (IDs: 1, 2, 3, 4)
- Refund transactions: 0
- Refund rate: 0/4 = 0% (less than 20%)
- Active period: Jan 5 to Feb 20 = 46 days (at least 30 days)
- Qualifies as loyal
Customer 102:
- Purchase transactions: 3 (IDs: 5, 6, 9)
- Refund transactions: 2 (IDs: 7, 8)
- Refund rate: 2/5 = 40% (exceeds 20%)
- Not loyal
Customer 103:
- Purchase transactions: 3 (IDs: 10, 11, 12)
- Refund transactions: 0
- Refund rate: 0/3 = 0% (less than 20%)
- Active period: Jan 1 to Jan 3 = 2 days (less than 30 days)
- Not loyal
Customer 104:
- Purchase transactions: 5 (IDs: 13, 14, 15, 16, 17)
- Refund transactions: 1 (ID: 18)
- Refund rate: 1/6 = 16.67% (less than 20%)
- Active period: Jan 1 to Mar 15 = 73 days (at least 30 days)
- Qualifies as loyal

The result table is ordered by customer_id in ascending order.

7:27

Still stuck?Read the written solution, or watch a walkthrough.

Open solution

LanguageLoading editor…

1# Write your MySQL query statement below

Starter codeMySQLSpaces: 4

Examples

Case 1

Input

{"headers":{"customer_transactions":["transaction_id","customer_id","transaction_date","amount","transaction_type"]},"rows":{"customer_transactions":[[1,101,"2024-01-05",150.00,"purchase"],[2,101,"2024-01-15",200.00,"purchase"],[3,101,"2024-02-10",180.00,"purchase"],[4,101,"2024-02-20",250.00,"purchase"],[5,102,"2024-01-10",100.00,"purchase"],[6,102,"2024-01-12",120.00,"purchase"],[7,102,"2024-01-15",80.00,"refund"],[8,102,"2024-01-18",90.00,"refund"],[9,102,"2024-02-15",130.00,"purchase"],[10,103,"2024-01-01",500.00,"purchase"],[11,103,"2024-01-02",450.00,"purchase"],[12,103,"2024-01-03",400.00,"purchase"],[13,104,"2024-01-01",200.00,"purchase"],[14,104,"2024-02-01",250.00,"purchase"],[15,104,"2024-02-15",300.00,"purchase"],[16,104,"2024-03-01",350.00,"purchase"],[17,104,"2024-03-10",280.00,"purchase"],[18,104,"2024-03-15",100.00,"refund"]]}}

Your code is saved on this device.Solution