An online retail company wants to identify customers who are at risk of reducing or stopping their future purchases.
The company maintains customer information, order details, and support-ticket details. Your task is to analyse the data and print the customers whose churn risk level is HIGH or MEDIUM.
The churn risk must be calculated using customer activity, return behaviour, support-ticket priority, and comparison with other customers in the same segment.
Customers Table:
| Field | Description |
|---|---|
| customerId | Unique identifier of the customer |
| customerName | Name of the customer |
| segment | Customer category assigned by the company. A customer is compared only with customers belonging to the same segment when calculating segmentAverageSpend. Customer segment: BASIC, SILVER, GOLD, or PLATINUM |
Orders Table:
| Field | Description |
|---|---|
| orderId | Unique identifier of the order |
| customerId | Customer who placed the order |
| orderDay | Day number on which the order was placed |
| amount | Order amount |
| status |
| Order status: COMPLETED or RETURNED |
Support Tickets Table:
| Field | Description |
|---|---|
| ticketId | Unique identifier of the support ticket |
| customerId | Customer who raised the ticket |
| priority | Ticket priority: LOW, MEDIUM, or HIGH |
For every customer, calculate the following values using only valid records:
completedOrderCount: Count the number of valid orders whose status is COMPLETED.
returnedOrderCount: Count the number of valid orders whose status is RETURNED.
validOrderCount: Count the total number of valid orders.
completedSpend: Calculate the sum of amounts from valid orders whose status is COMPLETED.
latestCompletedOrderDay: Find the latest orderDay among valid orders whose status is COMPLETED.
inactiveDays:
inactiveDays = referenceDay - latestCompletedOrderDay
If completedOrderCount is 0:
inactiveDays = referenceDay + 1
returnPercentage:
returnPercentage = (returnedOrderCount * 100) // validOrderCount
If validOrderCount is 0:
returnPercentage = 0
supportTicketWeight:
| Priority | Weight |
|---|---|
| LOW | 1 |
| MEDIUM | 2 |
| HIGH | 3 |
Calculate the sum of ticket weights of all valid support tickets raised by the customer.
Segment Average Spend:
segmentAverageSpend = total completedSpend of all customers in that segment // number of customers in that segment (use integer division).completedSpend < segmentAverageSpend.Churn Risk Score: Calculate the churn risk score for every customer.
| Condition | Score Added |
|---|---|
| If inactiveDays >= 90 | 5 |
| If completedOrderCount = 0 | 3 |
| If returnPercentage >= 40 | 2 |
| If completedSpend < segmentAverageSpend | 2 |
| Add supportTicketWeight | As calculated |
inactiveScore = 5 if inactiveDays >= 90; otherwise 0
noCompletedOrderScore = 3 if completedOrderCount = 0; otherwise 0
returnScore = 2 if returnPercentage >= 40; otherwise 0
lowSpendScore = 2 if completedSpend < segmentAverageSpend; otherwise 0
riskScore = inactiveScore + noCompletedOrderScore + returnScore + lowSpendScore + supportTicketWeight
| Risk Score | Risk Level |
|---|---|
| 8 or more | HIGH |
| 5 to 7 | MEDIUM |
| Less than 5 | LOW |
Only customers with HIGH or MEDIUM risk must be printed.
i) 2 <= n <= 100000 ii) 0 <= m <= 200000 iii) 0 <= p <= 200000 iv) 1 <= referenceDay <= 1000000 v) -1000000 <= orderDay <= 1000000 vi) -1000000000 <= amount <= 1000000000
The first line contains an integer referenceDay, representing the fixed day on which the customer activity analysis is performed.
The second, third, and fourth lines contain the number of records in the Customers table n, Orders table m, and Support Tickets table p, respectively.
The next n lines contain the Customers table details in the following format:
<customerId><space><customerName><space><segment>
The next m lines contain the Orders table details in the following format:
<orderId><space><customerId><space><orderDay><space><amount><space><status>
The next p lines contain the Support Tickets table details in the following format:
<ticketId><space><customerId><space><priority>
Print all eligible customers in the following format:
CustomerName-RiskLevel-RiskScore-InactiveDays
If multiple customers must be displayed, separate their details using a hash symbol (#).
Sort the output using the following sequence:
200
3
4
2
C100 Asha BASIC
C200 Bharat BASIC
C300 Charan GOLD
O1 C100 80 100 COMPLETED
O2 C100 90 50 RETURNED
O3 C200 195 400 COMPLETED
O4 C300 100 250 COMPLETED
T1 C100 HIGH
T2 C300 LOW
Asha-HIGH-12-120#Charan-MEDIUM-6-100
Explanation 1: For Asha: completedOrderCount = 1, returnedOrderCount = 1, validOrderCount = 2, completedSpend = 100, inactiveDays = 200 - 80 = 120, returnPercentage = (1 * 100) // 2 = 50, supportTicketWeight = 3, segmentAverageSpend for BASIC = (100 + 400) // 2 = 250. Asha is a low-spend customer. inactiveScore = 5, returnScore = 2, lowSpendScore = 2, riskScore = 5 + 0 + 2 + 2 + 3 = 12 → HIGH.
120
4
6
5
C1 Dev BASIC
C2 Esha BASIC
C3 Farah SILVER
C4 Gopi SILVER
O1 C1 110 100 COMPLETED
O2 C2 100 50 RETURNED
O3 C2 121 70 COMPLETED
O4 C3 30 200 RETURNED
O5 X9 10 99 COMPLETED
O6 C4 20 -10 COMPLETED
T1 C2 MEDIUM
T2 C2 HIGH
T3 C3 LOW
T4 C1 URGENT
T5 X9 HIGH
Esha-HIGH-17-121#Farah-HIGH-11-121#Gopi-HIGH-8-121
Explanation 2: The order of Esha on day 121 is invalid because it is later than the reference day 120. The order of customer X9 is invalid because the customer does not exist. The completed order of Gopi is invalid because its amount is negative.
Flipkart Grid 8.0 • Pending