Customer Purchasing Behavior Analysis - Practice Coding Problems

Customer Purchasing Behavior Analysis - Problem

Database Medium

You're building a comprehensive customer analytics system for an e-commerce platform! Given two database tables containing transaction history and product information, you need to analyze customer purchasing behavior and calculate key metrics that drive business decisions.

Your task is to generate a detailed customer profile for each customer that includes:

Financial Metrics: Total spending, average transaction amount
Engagement Metrics: Number of transactions, unique product categories explored
Behavioral Insights: Most frequently purchased category (with recency tiebreaker)
Loyalty Score: A custom formula combining transaction frequency and spending: (transactions × 10) + (total_amount ÷ 100)

The challenge lies in handling tied categories - when a customer purchases equally from multiple categories, you must select the category from their most recent transaction. Results should be ordered by loyalty score (descending), then by customer ID (ascending).

This problem mimics real-world customer segmentation and loyalty program analysis used by companies like Amazon, Netflix, and Spotify!

Input & Output

example_1.sql — Basic Customer Analysis

$ Input: Transactions: [(1,101,1,'2023-01-01',100.00), (2,101,2,'2023-01-15',150.00), (5,101,3,'2023-02-10',200.00)]\nProducts: [(1,'A',100.00), (2,'B',150.00), (3,'C',200.00)]

› Output: customer_id=101, total_amount=450.00, transaction_count=3, unique_categories=3, avg_transaction_amount=150.00, top_category='C', loyalty_score=34.50

💡 Note: Customer 101 has 3 transactions totaling $450. Since each category (A,B,C) has 1 purchase, we choose category C as it has the most recent transaction (2023-02-10). Loyalty score = (3×10) + (450÷100) = 34.50

example_2.sql — Tie-Breaking Scenario

$ Input: Transactions: [(3,102,1,'2023-01-01',100.00), (4,102,3,'2023-01-22',200.00)]\nProducts: [(1,'A',100.00), (3,'C',200.00)]

› Output: customer_id=102, total_amount=300.00, transaction_count=2, unique_categories=2, avg_transaction_amount=150.00, top_category='C', loyalty_score=23.00

💡 Note: Customer 102 has equal purchases in categories A and C (1 each). The tie is broken by recency - category C was purchased on 2023-01-22 vs category A on 2023-01-01. Loyalty score = (2×10) + (300÷100) = 23.00

example_3.sql — Single Category Customer

$ Input: Transactions: [(6,103,1,'2023-01-05',100.00), (7,103,1,'2023-01-10',100.00)]\nProducts: [(1,'A',100.00)]

› Output: customer_id=103, total_amount=200.00, transaction_count=2, unique_categories=1, avg_transaction_amount=100.00, top_category='A', loyalty_score=22.00

💡 Note: Customer 103 only purchases from category A (2 transactions). No tie-breaking needed. Loyalty score = (2×10) + (200÷100) = 22.00

Visualization

Tap to expand

Understanding the Visualization

Data Enrichment

JOIN transaction records with product catalog to add category information - like linking receipts to store departments

Pattern Analysis

Use window functions to analyze shopping patterns: frequency of category purchases and recency of visits

Tie-Breaking Logic

When customers shop equally across categories, choose their most recent preference - mimicking real customer behavior

Loyalty Scoring

Calculate composite loyalty score combining transaction frequency (engagement) and spending (value) - like airline miles systems

Key Takeaway

🎯 Key Insight: Use window functions with proper ORDER BY clauses to handle tie-breaking elegantly - ORDER BY frequency DESC, recency DESC ensures the most frequently purchased category wins, with most recent transaction breaking ties. This mimics real customer preference analysis!

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single scan of joined tables (n) plus sorting for window functions and final ORDER BY

⚡ Linearithmic

Space Complexity

O(k)

Where k is the number of unique customers for intermediate window function results

✓ Linear Space

Constraints

1 ≤ transaction_id ≤ 10⁶
1 ≤ customer_id ≤ 10⁴
1 ≤ product_id ≤ 10³
0.01 ≤ amount ≤ 10⁴
transaction_date is in 'YYYY-MM-DD' format
Each customer will have at least 1 transaction
Product categories are non-empty varchar strings
All amounts should be rounded to 2 decimal places

Asked in

a Amazon 85 f Meta 72 G Google 68 N Netflix 45 ♫ Spotify 38

The optimal solution uses window functions and CTEs to analyze customer behavior in a single efficient pass. Key insights: JOIN tables first to enrich transaction data, use ROW_NUMBER() with proper ORDER BY for tie-breaking (frequency DESC, recency DESC), and leverage conditional aggregation to calculate multiple metrics simultaneously. The loyalty score formula (transactions × 10) + (total_amount ÷ 100) provides a balanced measure of customer value combining engagement and spending.

Common Approaches

Approach	Time	Space	Notes
✓ Single Query with Window Functions (Optimal)	O(n log n)	O(k)	Use window functions and conditional aggregation to calculate all metrics in one pass
Multiple Subqueries Approach	O(n × m)	O(k)	Use separate subqueries for each metric calculation

Single Query with Window Functions (Optimal) — Algorithm Steps

Join Transactions with Products to get category information
Use window functions to rank categories by frequency and recency
Apply conditional aggregation to calculate multiple metrics simultaneously
Use FIRST_VALUE() to get the top category after proper ranking
Calculate loyalty score and apply rounding in the final SELECT
Order results by loyalty score and customer ID

Visualization

Tap to expand

Step-by-Step Walkthrough

Join Tables

Join Transactions with Products to enrich data with categories

Window Functions

Apply window functions to rank categories by frequency and recency

Aggregate Metrics

Calculate all customer metrics using conditional aggregation

Final Output

Apply formatting and sorting to produce final results

Code -

solution.c — C

// Optimal SQL query representation
#include <stdio.h>

int main() {
    printf("%s\n", 
    "WITH customer_transactions AS (\n"
    "    SELECT t.customer_id, t.amount, t.transaction_date, p.category\n"
    "    FROM Transactions t\n"
    "    JOIN Products p ON t.product_id = p.product_id\n"
    "),\n"
    "category_ranks AS (\n"
    "    SELECT customer_id, category,\n"
    "           COUNT(*) as freq,\n"
    "           MAX(transaction_date) as latest_date,\n"
    "           ROW_NUMBER() OVER (\n"
    "               PARTITION BY customer_id \n"
    "               ORDER BY COUNT(*) DESC, MAX(transaction_date) DESC\n"
    "           ) as rn\n"
    "    FROM customer_transactions\n"
    "    GROUP BY customer_id, category\n"
    ")\n"
    "SELECT \n"
    "    ct.customer_id,\n"
    "    ROUND(SUM(ct.amount), 2) as total_amount,\n"
    "    COUNT(*) as transaction_count,\n"
    "    COUNT(DISTINCT ct.category) as unique_categories,\n"
    "    ROUND(AVG(ct.amount), 2) as avg_transaction_amount,\n"
    "    cr.category as top_category,\n"
    "    ROUND((COUNT(*) * 10) + (SUM(ct.amount) / 100), 2) as loyalty_score\n"
    "FROM customer_transactions ct\n"
    "JOIN category_ranks cr ON ct.customer_id = cr.customer_id AND cr.rn = 1\n"
    "GROUP BY ct.customer_id, cr.category\n"
    "ORDER BY loyalty_score DESC, customer_id ASC");
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single scan of joined tables (n) plus sorting for window functions and final ORDER BY

⚡ Linearithmic

Space Complexity

O(k)

Where k is the number of unique customers for intermediate window function results

✓ Linear Space

Constraints

1 ≤ transaction_id ≤ 10⁶
1 ≤ customer_id ≤ 10⁴
1 ≤ product_id ≤ 10³
0.01 ≤ amount ≤ 10⁴
transaction_date is in 'YYYY-MM-DD' format
Each customer will have at least 1 transaction
Product categories are non-empty varchar strings
All amounts should be rounded to 2 decimal places

54.2K Views

High Frequency

~25 min Avg. Time

1.8K 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Single Query with Window Functions (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler