Customer Purchasing Behavior Analysis - Problem

You are given two tables: Transactions and Products. Write a SQL query 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 per transaction
  • 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)

Requirements:

  • Round total_amount, avg_transaction_amount, and loyalty_score to 2 decimal places
  • Return results ordered by loyalty_score in descending order, then by customer_id in ascending order

Table Schema

Transactions
Column Name Type Description
transaction_id PK int Unique identifier for each transaction
customer_id int Customer identifier
product_id int Product identifier
transaction_date date Date of the transaction
amount decimal Transaction amount
Primary Key: transaction_id
Products
Column Name Type Description
product_id PK int Unique identifier for each product
category varchar Product category
price decimal Product price
Primary Key: product_id

Input & Output

Example 1 — Customer Behavior Analysis
Input Tables:
Transactions
transaction_id customer_id product_id transaction_date amount
1 101 1 2023-01-01 100
2 101 2 2023-01-15 150
3 102 1 2023-01-01 100
4 102 3 2023-01-22 200
5 101 3 2023-02-10 200
Products
product_id category price
1 A 100
2 B 150
3 C 200
Output:
customer_id total_amount transaction_count unique_categories avg_transaction_amount top_category loyalty_score
101 450 3 3 150 C 34.5
102 300 2 2 150 C 23
💡 Note:

Customer 101: Made 3 transactions (categories A, B, C) totaling $450. Most recent category is C (2023-02-10). Loyalty score: (3 × 10) + (450 ÷ 100) = 34.50

Customer 102: Made 2 transactions (categories A, C) totaling $300. Most recent category is C (2023-01-22). Loyalty score: (2 × 10) + (300 ÷ 100) = 23.00
Example 2 — Single Category Customer
Input Tables:
Transactions
transaction_id customer_id product_id transaction_date amount
1 201 1 2023-01-01 100
2 201 1 2023-01-15 100
Products
product_id category price
1 A 100
Output:
customer_id total_amount transaction_count unique_categories avg_transaction_amount top_category loyalty_score
201 200 2 1 100 A 22
💡 Note:

Customer 201 only purchased from category A twice. Since there's only one category, it's automatically the top category. Loyalty score: (2 × 10) + (200 ÷ 100) = 22.00

Constraints

  • 1 ≤ transaction_id ≤ 1000
  • 1 ≤ customer_id ≤ 100
  • 1 ≤ product_id ≤ 50
  • transaction_date is a valid date
  • amount > 0

Visualization

Tap to expand
Customer Purchasing Behavior Analysis INPUT DATA transactions[] customer_id: "C001" amount: 150.00 category: "Electronics" customer_id: "C001" amount: 45.50 category: "Groceries" customer_id: "C002" amount: 200.00 category: "Clothing" ... more transactions ... Data Fields - customer_id (string) - transaction_amount (float) - category (string) - timestamp (date) ALGORITHM STEPS 1 Group by Customer Use HashMap to group transactions by customer_id 2 Calculate Metrics Sum amounts, count txns, track unique categories 3 Find Frequencies Track category counts, identify most frequent 4 Compute Loyalty Score = txn_count * avg * category_diversity HashMap Structure "C001" --> [txns, sum, cats] "C002" --> [txns, sum, cats] "C003" --> [txns, sum, cats] FINAL RESULT Customer: C001 total_spent: $450.75 txn_count: 8 unique_cats: 4 avg_amount: $56.34 top_category: Electronics loyalty_score: 1802.88 Customer: C002 total_spent: $890.20 txn_count: 12 unique_cats: 6 avg_amount: $74.18 top_category: Clothing loyalty_score: 5340.96 OK Analysis Complete! Key Insight: Optimal O(n) Single-Pass Solution Using a HashMap with customer_id as key allows us to process all transactions in a single pass (O(n) time). Each customer's data is aggregated incrementally: running totals, category frequency maps, and Set for unique categories. The loyalty score formula (txn_count * avg_amount * unique_categories) rewards both frequency and diversity of purchases. TutorialsPoint - Customer Purchasing Behavior Analysis | Optimal Solution

Related Problems

Asked in
Amazon 28 Meta 22 Google 18 Microsoft 15
23.5K Views
Medium Frequency
~25 min Avg. Time
847 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen