Customer Purchasing Behavior Analysis - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase6 min readAsked at: Amazon

Problem Statement

Table: Transactions

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| transaction_id   | int     |
| customer_id      | int     |
| product_id       | int     |
| transaction_date | date    |
| amount           | decimal |
+------------------+---------+
transaction_id is the unique identifier for this table.
Each row of this table contains information about a transaction, including the customer ID, product ID, date, and amount spent.

Table: Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| category    | varchar |
| price       | decimal |
+-------------+---------+
product_id is the unique identifier for this table.
Each row of this table contains information about a product, including its category and price.

Write a solution to analyze customer purchasing behavior. For each customer, calculate:

The total amount spent.
The number of transactions.
The number of unique product categories purchased.
The average amount spent.
The most frequently purchased product category (if there is a tie, choose the one with the most recent transaction).
A loyalty score defined as: (Number of transactions * 10) + (Total amount spent / 100).

Round total_amount, avg_transaction_amount, and loyalty_score to 2 decimal places.

Return the result table ordered by loyalty_score in descending order, then by customer_id in ascending order.

The query result format is in the following example.

Example:

Input:

Transactions table:

+----------------+-------------+------------+------------------+--------+
| transaction_id | customer_id | product_id | transaction_date | amount |
+----------------+-------------+------------+------------------+--------+
| 1              | 101         | 1          | 2023-01-01       | 100.00 |
| 2              | 101         | 2          | 2023-01-15       | 150.00 |
| 3              | 102         | 1          | 2023-01-01       | 100.00 |
| 4              | 102         | 3          | 2023-01-22       | 200.00 |
| 5              | 101         | 3          | 2023-02-10       | 200.00 |
+----------------+-------------+------------+------------------+--------+

Products table:

+------------+----------+--------+
| product_id | category | price  |
+------------+----------+--------+
| 1          | A        | 100.00 |
| 2          | B        | 150.00 |
| 3          | C        | 200.00 |
+------------+----------+--------+

Output:

+-------------+--------------+-------------------+-------------------+------------------------+--------------+---------------+
| customer_id | total_amount | transaction_count | unique_categories | avg_transaction_amount | top_category | loyalty_score |
+-------------+--------------+-------------------+-------------------+------------------------+--------------+---------------+
| 101         | 450.00       | 3                 | 3                 | 150.00                 | C            | 34.50         |
| 102         | 300.00       | 2                 | 2                 | 150.00                 | C            | 23.00         |
+-------------+--------------+-------------------+-------------------+------------------------+--------------+---------------+

Explanation:

For customer 101:
- Total amount spent: 100.00 + 150.00 + 200.00 = 450.00
- Number of transactions: 3
- Unique categories: A, B, C (3 categories)
- Average transaction amount: 450.00 / 3 = 150.00
- Top category: C (Customer 101 made 1 purchase each in categories A, B, and C. Since the count is the same for all categories, we choose the most recent transaction, which is category C on 2023-02-10)
- Loyalty score: (3 * 10) + (450.00 / 100) = 34.50
For customer 102:
- Total amount spent: 100.00 + 200.00 = 300.00
- Number of transactions: 2
- Unique categories: A, C (2 categories)
- Average transaction amount: 300.00 / 2 = 150.00
- Top category: C (Customer 102 made 1 purchase each in categories A and C. Since the count is the same for both categories, we choose the most recent transaction, which is category C on 2023-01-22)
- Loyalty score: (2 * 10) + (300.00 / 100) = 23.00

Note: The output is ordered by loyalty_score in descending order, then by customer_id in ascending order.

Approach Overview

Problem Overview: You need to analyze purchasing activity across customers and determine behavioral patterns from transactional data. The task usually involves aggregating purchases per customer, comparing them within a group, and returning rows that satisfy ranking or behavioral conditions.

Approach 1: Basic Aggregation with GROUP BY (O(n) time, O(1) extra space)

The first step most engineers try is aggregating purchases using GROUP BY. You compute metrics like total orders, total spend, or purchase counts per customer. This works when the problem only asks for simple summaries. However, it breaks down when you must compare rows within the same customer group or find patterns such as "top purchase", "most recent transaction", or ranked activity. Basic aggregation collapses rows and loses row-level detail, so additional logic becomes difficult.

Approach 2: Grouping + Window Functions + Join (O(n log n) time, O(n) space)

The production-ready solution combines aggregation, window functions, and joins. First, compute per-customer metrics using GROUP BY. Then apply a window function like ROW_NUMBER(), RANK(), or DENSE_RANK() with PARTITION BY customer_id. This keeps row-level data while also allowing comparisons inside each customer partition. Window operations internally require sorting within each partition, which typically leads to O(n log n) time complexity.

After computing rankings or behavioral indicators, join the derived result back to the original table or filtered aggregates. The join step retrieves only the rows that match the required behavior (for example, the highest purchase, most recent order, or qualifying transaction pattern). This structure is flexible and readable, which is why it appears frequently in real-world analytics queries.

This approach relies heavily on SQL analytics features such as PARTITION BY and ordered windows. If you want to deepen these concepts, review SQL Window Functions, GROUP BY aggregation, and SQL Joins. Understanding how these three pieces interact is key to solving most database interview questions.

Recommended for interviews: The grouping + window function approach is the expected solution. Starting with a basic aggregation demonstrates you understand the dataset and metrics. Moving to window functions shows stronger SQL skills because you preserve row-level detail while computing ranked analytics. Interviewers typically look for correct partitioning logic, proper ordering inside the window function, and a clean join or filter that returns the final result.

Solution

First, we join the Transactions table with the Products table, recording the result in a temporary table T.

Next, we use the T table to calculate the transaction count and the most recent transaction date for each user in each category, saving the results in a temporary table P.

Then, we use the P table to calculate the ranking of transaction counts for each user in each category, saving the results in a temporary table R.

Finally, we use the T and R tables to calculate the total transaction amount, transaction count, unique category count, average transaction amount, most frequently purchased category, and loyalty score for each user, and return the results in descending order of loyalty score and ascending order of user ID.

Code

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

Approach	Time	Space	When to Use
Basic Aggregation with GROUP BY	O(n)	O(1)	When only summary metrics per customer are required without row-level comparison
Grouping + Window Functions + Join	O(n log n)	O(n)	When you must rank, compare, or filter transactions within each customer partition

Video Solution

Leetcode MEDIUM 3230 - When NOT Use COALESCE - Customer Purchasing Behavior | Everyday Data Science • Everyday Data Science • 822 views views

Frequently Asked Questions

Is Customer Purchasing Behavior Analysis easy or hard?

The problem is rated Medium because it requires combining multiple SQL concepts. Basic aggregation alone is straightforward, but the correct solution requires understanding window functions, partitioning logic, and how to join derived results back to the base table.

Customer Purchasing Behavior Analysis Python/Java solution

This problem is designed as a SQL query challenge rather than a Python or Java algorithm implementation. The solution is typically written in MySQL using GROUP BY, window functions like ROW_NUMBER or RANK, and joins to filter the final result set.

How to solve Customer Purchasing Behavior Analysis in O(n)?

Pure O(n) solutions are only possible for simple aggregation using GROUP BY when no ranking or intra-group comparison is required. Once the query needs ordering within partitions, window functions such as ROW_NUMBER or RANK are necessary, which generally introduce O(n log n) complexity due to sorting.

What is the best approach for Customer Purchasing Behavior Analysis?

The most effective solution uses SQL grouping combined with window functions and joins. First aggregate purchase metrics per customer, then apply window functions such as ROW_NUMBER or RANK with PARTITION BY to compare transactions within each customer group. Finally filter or join the ranked results to produce the required rows. This approach handles complex behavioral comparisons efficiently.

Is Customer Purchasing Behavior Analysis asked at Google/Amazon/Meta?

Database questions involving aggregations, joins, and window functions frequently appear in interviews at companies like Amazon, Google, and Meta. While the exact problem may vary, the pattern of analyzing transactional data using GROUP BY and window functions is a common SQL interview theme.

What data structure is used in Customer Purchasing Behavior Analysis?

The problem is solved using relational database operations rather than traditional in-memory data structures. Key SQL mechanisms include GROUP BY for aggregation, window functions for ranking within partitions, and JOIN operations to combine filtered results with the original dataset.

What is the time complexity of Customer Purchasing Behavior Analysis?

The optimal SQL approach runs in approximately O(n log n) time because window functions require sorting rows within each partition. Aggregation with GROUP BY scans the dataset in O(n) time, while the window operation introduces the log n sorting cost. Space complexity is typically O(n) due to intermediate result sets created during ranking.

Ready to solve this problem?

Practice Customer Purchasing Behavior Analysis with our built-in code editor and test cases.

Practice on FleetCode

Combine Two Tables

Second Highest Salary

Problem Info

DifficultyMedium

Acceptance37.8%

Approaches1

Reading time6 min

Asked at

Amazon

Practice this problem

Open in Editor