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 AnalysisInput: Customer Transactionscustomer_idamountcategory101100.00A101150.00B101200.00CGROUP BYWINDOW FUNCOutput: Customer Metricscustomertotalcountcategoriesavg_amounttop_catloyalty101450.0033150.00C34.50Analysis Process:Step 1: JoinTransactions +ProductsGet categoriesStep 2: AggregateSUM, COUNT,AVG per customerBasic metricsStep 3: RankROW_NUMBER()Top categoryby frequencyStep 4: CalculateLoyalty score(count × 10) +(total ÷ 100)
Understanding the Visualization
1
Input Tables
Transactions and Products tables
2
Join & Aggregate
Calculate customer metrics
3
Output Analysis
Customer behavior insights
Key Takeaway
🎯 Key Insight: Use window functions with ORDER BY to handle tie-breaking when determining the most frequent category

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