3657. Find Loyal Customers - SQL Solution

The problem

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.

Approach overview

Problem Overview: You have a table of customer transactions, each marked as a purchase or a refund. You need to return the customers who meet the loyalty criteria defined in the problem statement, based on how many purchases and refunds each one has.

Approach 1: Correlated Subqueries per Customer (O(n * m) time, O(1) extra space)

Start with the distinct customer list, then run a separate subquery for each customer to count purchases and refunds. This works because each subquery is a self-contained check against the transaction table. The cost is that the engine re-scans the table for every customer, so it degrades quickly on large data. Use it only to verify logic on small samples.

Approach 2: Single-Pass Conditional Aggregation with GROUP BY and HAVING (O(n) time, O(k) space)

Group all rows by customer_id once. Inside the aggregate, use SUM(CASE WHEN transaction_type = 'purchase' THEN 1 ELSE 0 END) to count purchases and the same pattern with 'refund' to count refunds. Put the loyalty condition in HAVING, so only qualifying groups survive. The key insight is that one scan produces every metric you need, so you never touch a row twice. Here n is the number of transaction rows and k is the number of distinct customers. This is the default approach and the one you should write by default in MySQL.

Approach 3: Pandas groupby with Boolean Masks (O(n) time, O(k) space)

In Pandas, build two Boolean masks, one for purchases and one for refunds. Then call groupby('customer_id') and count each mask using sum() on the boolean column, or use .agg with named aggregations. Filter the resulting DataFrame with the same thresholds you would put in HAVING, then sort and select the required columns. This mirrors the SQL logic exactly, which makes it easy to cross-check your answer.

Recommended for interviews: Interviewers expect the single-pass GROUP BY with conditional aggregation. The correlated subquery version shows you understand the requirement, but the grouped version shows you can reduce the work to one scan and express the condition cleanly in HAVING. Explain that choice out loud, since it is what separates a passing answer from a strong one.

Related practice: database problems and aggregation patterns cover this same GROUP BY and HAVING technique.

Solution

Thinking

Loyalty depends on volume, refund rate, and active span, so a single aggregation per customer_id is enough.

Compute transaction count, refund count, and the first and last dates. The span is the day difference; the refund ratio is refunds over transactions.

Keep customers with at least three transactions, a refund ratio below 0.2, and a span of at least 30 days, ordered by id.

MySQL

# Write your MySQL query statement below
SELECT customer_id
FROM customer_transactions
GROUP BY 1
HAVING
    COUNT(1) >= 3
    AND SUM(transaction_type = 'refund') / COUNT(1) < 0.2
    AND DATEDIFF(MAX(transaction_date), MIN(transaction_date)) >= 30
ORDER BY 1;

Pandas

import pandas as pd


def find_loyal_customers(customer_transactions: pd.DataFrame) -> pd.DataFrame:
    customer_transactions["transaction_date"] = pd.to_datetime(
        customer_transactions["transaction_date"]
    )
    grouped = customer_transactions.groupby("customer_id")
    agg_df = grouped.agg(
        total_transactions=("transaction_type", "size"),
        refund_count=("transaction_type", lambda x: (x == "refund").sum()),
        min_date=("transaction_date", "min"),
        max_date=("transaction_date", "max"),
    ).reset_index()
    agg_df["date_diff"] = (agg_df["max_date"] - agg_df["min_date"]).dt.days
    agg_df["refund_ratio"] = agg_df["refund_count"] / agg_df["total_transactions"]
    result = (
        agg_df[
            (agg_df["total_transactions"] >= 3)
            & (agg_df["refund_ratio"] < 0.2)
            & (agg_df["date_diff"] >= 30)
        ][["customer_id"]]
        .sort_values("customer_id")
        .reset_index(drop=True)
    )
    return result

Try this approach in the editor

Complexity analysis

Approach	Time	Space	When to Use
Correlated Subqueries per Customer	O(n * m)	O(1) extra	Small datasets or verifying logic only
Single-Pass Conditional Aggregation (GROUP BY + HAVING)	O(n)	O(k)	Default for MySQL and interviews
Pandas groupby with Boolean Masks	O(n)	O(k)	Dataframe workflows in Python

Frequently asked questions

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.

Find Loyal Customers solution and explanation