Customer Churn Risk Analysis - Flipkart Grid 8.0

Problem Preview

"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. ## Data Structures **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 | ## Valid Record Rules - An order is valid only when all of the following conditions are satisfied: - The customerId exists in the Customers table. - The status is either COMPLETED or RETURNED. - The orderDay is between 1 and referenceDay, inclusive. - The amount is greater than 0. - Invalid orders must be ignored completely. - A support ticket is valid only when: - The customerId exists in the Customers table. - Its priority is LOW, MEDIUM, or HIGH. - Invalid support tickets must be ignored. - Every order record and support-ticket record must be processed independently. If identical records appear more than once, each occurrence must be counted separately. ## Features to Calculate For every customer, calculate the following values using only valid records: 1. **completedOrderCount**: Count the number of valid orders whose status is COMPLETED. 2. **returnedOrderCount**: Count the number of valid orders whose status is RETURNED. 3. **validOrderCount**: Count the total number of valid orders. 4. **completedSpend**: Calculate the sum of amounts from valid orders whose status is COMPLETED. 5. **latestCompletedOrderDay**: Find the latest orderDay among valid orders whose status is COMPLETED. 6. **inactiveDays**: ``` inactiveDays = referenceDay - latestCompletedOrderDay If completedOrderCount is 0: inactiveDays = referenceDay + 1 ``` 7. **returnPercentage**: ``` returnPercentage = (returnedOrderCount * 100) // validOrderCount If validOrderCount is 0: returnPercentage = 0 ``` 8. **supportTicketWeight**: | Priority | Weight | |---|---| | LOW | 1 | | MEDIUM | 2 | | HIGH | 3 | Calculate the sum of ticket weights of all valid support tickets raised by the customer. 9. **Segment Average Spend**: - For each segment, calculate: `segmentAverageSpend = total completedSpend of all customers in that segment // number of customers in that segment` (use integer division). - All customers in the segment must be considered, including customers with no orders. - A customer is a low-spend customer when: `completedSpend < segmentAverageSpend`. - A customer whose completedSpend equals the segment average is not considered low-spend. 10. **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 ``` ## Churn Risk Level | 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. ## Constraints i) 2 <= n <= 100000 ii) 0 <= m <= 200000 iii) 0 <= p <= 200000 iv) 1 <= referenceDay <= 1000000 v) -1000000 <= orderDay <= 1000000 vi) -1000000000 <= amount <= 1000000000 ## Input Format 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>` ## Output Format 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: - HIGH risk customers before MEDIUM risk customers. - Higher risk score first. - Higher inactive days first. - Original input sequence of the Customers table when all the above values are equal. ## Sample Input 1 ``` 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 ``` ## Sample Output 1 ``` 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. ## Sample Input 2 ``` 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 ``` ## Sample Output 2 ``` 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."