101
0/304
Loading content...
A customer analytics team wants a reusable summary table that ranks account engagement quality and category preference.
Table: transactions
Table: products
Task: For each customer with at least one transaction, compute:
Top-category tie-breaking:
Rounding:
Output columns (exact order):
Final sorting:
Supported submission environments:
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}
]Both customers have category-frequency ties, and recency decides top_category as C in each case.
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}
]Customer 201 has equal frequency in Alpha and Beta, so latest transaction date breaks the tie in favor of Beta.
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 |[
{"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}
]Average amount uses 2-decimal half-up rounding: 0.05 / 3 = 0.0166..., which rounds to 0.02.
Constraints