Customer Purchase Behavior Command Center

Medium

A customer analytics team wants a reusable summary table that ranks account engagement quality and category preference. Table: transactions transaction_id: unique transaction identifier customer_id: customer identifier product_id: purchased product identifier transaction_date: transaction date in YYYY-MM-DD format amount: transaction amount Table: products product_id: unique product identifier category: product category label price: listed product price Task: For each customer with at least one transaction, compute: total_amount transaction_count unique_categories avg_transaction_amount top_category loyalty_score = (transaction_count * 10) + (total_amount / 100) Top-category tie-breaking: Highest category transaction count first. If tied, most recent transaction_date. If still tied, lexicographically smallest category. Rounding: total_amount, avg_transaction_amount, and loyalty_score must be rounded to 2 decimals. Output columns (exact order): customer_id total_amount transaction_count unique_categories avg_transaction_amount top_category loyalty_score Final sorting: loyalty_score descending customer_id ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

transactions:
| 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:
| product_id | category | price  |
|------------|----------|--------|
| 1          | A        | 100.00 |
| 2          | B        | 150.00 |
| 3          | C        | 200.00 |

Output

[
  {"customer_id":101,"total_amount":450.0,"transaction_count":3,"unique_categories":3,"avg_transaction_amount":150.0,"top_category":"C","loyalty_score":34.5},
  {"customer_id":102,"total_amount":300.0,"transaction_count":2,"unique_categories":2,"avg_transaction_amount":150.0,"top_category":"C","loyalty_score":23.0}
]

Explanation

Both customers have category-frequency ties, and recency decides top_category as C in each case.

Example 2

Input

transactions:
| transaction_id | customer_id | product_id | transaction_date | amount |
|----------------|-------------|------------|------------------|--------|
| 10             | 201         | 11         | 2024-01-01       | 10.00  |
| 11             | 201         | 12         | 2024-01-03       | 20.00  |
| 12             | 201         | 11         | 2024-01-07       | 30.00  |
| 13             | 201         | 12         | 2024-01-09       | 40.00  |
| 14             | 202         | 13         | 2024-01-01       | 50.00  |

products:
| product_id | category | price |
|------------|----------|-------|
| 11         | Alpha    | 10.00 |
| 12         | Beta     | 20.00 |
| 13         | Gamma    | 50.00 |

Output

[
  {"customer_id":201,"total_amount":100.0,"transaction_count":4,"unique_categories":2,"avg_transaction_amount":25.0,"top_category":"Beta","loyalty_score":41.0},
  {"customer_id":202,"total_amount":50.0,"transaction_count":1,"unique_categories":1,"avg_transaction_amount":50.0,"top_category":"Gamma","loyalty_score":10.5}
]

Explanation

Customer 201 has equal frequency in Alpha and Beta, so latest transaction date breaks the tie in favor of Beta.

Example 3

Input

transactions:
| transaction_id | customer_id | product_id | transaction_date | amount |
|----------------|-------------|------------|------------------|--------|
| 50             | 303         | 21         | 2025-03-10       | 0.02   |
| 51             | 303         | 22         | 2025-03-11       | 0.03   |
| 52             | 303         | 23         | 2025-03-11       | 0.00   |

products:
| product_id | category | price |
|------------|----------|-------|
| 21         | Delta    | 3.00  |
| 22         | Echo     | 4.00  |
| 23         | Delta    | 5.00  |

Output

[
  {"customer_id":303,"total_amount":0.05,"transaction_count":3,"unique_categories":2,"avg_transaction_amount":0.02,"top_category":"Delta","loyalty_score":30.0}
]

Explanation

Average amount uses 2-decimal half-up rounding: 0.05 / 3 = 0.0166..., which rounds to 0.02.

Constraints

1 <= number of rows in transactions <= 300000
1 <= number of rows in products <= 100000
transaction_id values are unique
product_id values are unique in products
transactions.product_id references products.product_id
1 <= customer_id <= 1000000000
1 <= product_id <= 1000000000
0 <= amount <= 100000000 and amount has at most 2 decimals
0 <= price <= 100000000 and price has at most 2 decimals
transaction_date is a valid date string in YYYY-MM-DD format
top_category tie-break order is frequency DESC, latest transaction_date DESC, category ASC
Round total_amount, avg_transaction_amount, loyalty_score to 2 decimals
Sort output by loyalty_score DESC, customer_id ASC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with required columns

transactions: | 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: | product_id | category | price | |------------|----------|--------| | 1 | A | 100.00 | | 2 | B | 150.00 | | 3 | C | 200.00 |

[ {"customer_id":101,"total_amount":450.0,"transaction_count":3,"unique_categories":3,"avg_transaction_amount":150.0,"top_category":"C","loyalty_score":34.5}, {"customer_id":102,"total_amount":300.0,"transaction_count":2,"unique_categories":2,"avg_transaction_amount":150.0,"top_category":"C","loyalty_score":23.0} ]

transactions: | transaction_id | customer_id | product_id | transaction_date | amount | |----------------|-------------|------------|------------------|--------| | 10 | 201 | 11 | 2024-01-01 | 10.00 | | 11 | 201 | 12 | 2024-01-03 | 20.00 | | 12 | 201 | 11 | 2024-01-07 | 30.00 | | 13 | 201 | 12 | 2024-01-09 | 40.00 | | 14 | 202 | 13 | 2024-01-01 | 50.00 | products: | product_id | category | price | |------------|----------|-------| | 11 | Alpha | 10.00 | | 12 | Beta | 20.00 | | 13 | Gamma | 50.00 |

[ {"customer_id":201,"total_amount":100.0,"transaction_count":4,"unique_categories":2,"avg_transaction_amount":25.0,"top_category":"Beta","loyalty_score":41.0}, {"customer_id":202,"total_amount":50.0,"transaction_count":1,"unique_categories":1,"avg_transaction_amount":50.0,"top_category":"Gamma","loyalty_score":10.5} ]

transactions: | transaction_id | customer_id | product_id | transaction_date | amount | |----------------|-------------|------------|------------------|--------| | 50 | 303 | 21 | 2025-03-10 | 0.02 | | 51 | 303 | 22 | 2025-03-11 | 0.03 | | 52 | 303 | 23 | 2025-03-11 | 0.00 | products: | product_id | category | price | |------------|----------|-------| | 21 | Delta | 3.00 | | 22 | Echo | 4.00 | | 23 | Delta | 5.00 |