▸case-01 Our SaaS company lost 5 high-tier enterprise accounts ($10k/mo each) out of 100 enterprise customers, while gaining 10 basic tier customers ($100/mo each) out of 1000 basic customers. We want to measure our monthly churn rate. If we only calculate customer count churn rate, it shows 1.36% (15 lost out of 1100), making retention look great. Show the SQL query formula to compute net MRR churn rate alongside logo churn rate to accurately report financial impact. | pass→pass | 28,182 | 23,107 | -18% | 1 | 1 | 0% | 4,962 | 4,502 | -9% | 0 | 0 | — |
▸case-02 We store user activity log events in PostgreSQL with user_id and activity_date, alongside a users table with signup_date. A common mistake is grouping by raw activity date, which doesn't align users by lifetime duration. Write a SQL query to build a monthly cohort retention matrix where cohort month is defined by signup month and cohort age is measured in months elapsed. | pass→pass | 23,215 | 19,035 | -18% | 1 | 1 | 0% | 3,968 | 3,367 | -15% | 0 | 0 | — |
▸case-03 We want to analyze time-to-churn for our SaaS subscribers who have varying subscription start dates and some active users who haven't churned yet. Base analysis often incorrectly treats active users as churned at current duration or excludes them entirely. Provide a Python script using lifelines to perform Kaplan-Meier survival estimation for customer retention. | pass→pass | 21,529 | 21,804 | +1% | 1 | 1 | 0% | 3,377 | 3,732 | +11% | 0 | 0 | — |
▸case-04 We are building a machine learning feature pipeline for user churn prediction over a 30-day target window. Base implementations calculate user activity features over the entire dataset history, causing data leakage from after the prediction cutoff date. Show how to construct feature aggregation windows in SQL or pandas relative to an explicit observation point (cutoff date). | pass→pass | 24,636 | 26,887 | +9% | 1 | 1 | 0% | 4,213 | 4,992 | +18% | 0 | 0 | — |
▸case-05 When calculating monthly churn rate for an e-commerce subscription service, subtracting mid-month new signups directly from the denominator distorts the metric. Show the standard formula and SQL logic for calculating monthly churn rate using average active subscriber count or start-of-period subscriber count. | pass→pass | 19,259 | 22,456 | +17% | 1 | 1 | 0% | 2,858 | 3,994 | +40% | 0 | 0 | — |
▸case-06 We trained a Random Forest model to predict churn probability for 50,000 customers. Evaluating the model using standard accuracy yields 92% because overall churn is 8%, hiding poor model performance. Write Python code using scikit-learn and matplotlib to evaluate the model using a cumulative gains or lift chart across customer deciles. | pass→pass | 21,072 | 25,746 | +22% | 1 | 1 | 0% | 3,521 | 4,859 | +38% | 0 | 0 | — |
▸case-07 Our payment processing logs report subscription cancellations caused by expired credit cards alongside explicit user cancellations. Mixing these into a single churn bucket leads to wrong mitigation tactics like product interventions for payment failures. Provide a data categorization query strategy in SQL that separates voluntary churn from involuntary (failed payment) churn. | fail→fail | 19,989 | 39,906 | +100% | 1 | 1 | 0% | 2,753 | 3,915 | +42% | 0 | 0 | — |
▸case-08 We want to identify which customer attributes (e.g., monthly login frequency, support ticket count, plan tier) significantly affect the hazard of customer churn over time. Write a Python snippet using CoxProportionalHazardsFitter to estimate hazard ratios for customer duration. | pass→pass | 10,441 | 12,041 | +15% | 1 | 1 | 0% | 2,389 | 2,881 | +21% | 0 | 0 | — |
▸case-09 When running our automated churn analysis CLI tool, the execution fails with a 'Configuration invalid' error message. What is the root cause defined in churn analytics execution tools and what is the required remedy? | fail→pass | 16,170 | 2,577 | -84% | 1 | 1 | 0% | 1,900 | 745 | -61% | 0 | 0 | — |
▸case-10 A churn analysis execution pipeline returns a 'Tool not found' failure during script initialization. What is the cause specified in churn tool troubleshooting guides, and how should it be resolved? | pass→pass | 13,326 | 7,092 | -47% | 1 | 1 | 0% | 2,232 | 681 | -69% | 0 | 0 | — |
▸case-11 Executing a churn analysis pipeline against customer database views returns 'Permission denied'. What is the root cause and standard resolution according to churn tool operational standards? | pass→pass | 11,650 | 15,713 | +35% | 1 | 1 | 0% | 1,915 | 2,231 | +17% | 0 | 0 | — |
▸case-12 A subscription business wants to present Net Revenue Retention (NRR) to investors. Analysts often confuse Gross Revenue Retention (GRR) with NRR by leaving out expansion revenue. Provide the mathematical formula and formula breakdown for Net Revenue Retention percentage. | fail→pass | 15,268 | 12,539 | -18% | 1 | 1 | 0% | 2,055 | 1,793 | -13% | 0 | 0 | — |
▸case-13 We generated a monthly cohort retention table containing retention percentages across 12 elapsed months for 12 signup cohorts. Write Python Seaborn code to visualize this cohort retention grid as an annotated heatmap. | pass→fail | 16,679 | 15,687 | -6% | 1 | 1 | 0% | 2,294 | 3,485 | +52% | 0 | 0 | — |
▸case-14 In a dataset of 100,000 customers, only 3,000 churned within the last quarter. Training a binary classifier without addressing this 3% positive class imbalance results in a model that predicts zero churn for everyone. Provide two data-level or model-level strategies to handle class imbalance in churn prediction. | pass→pass | 16,921 | 17,189 | +2% | 1 | 1 | 0% | 2,108 | 2,541 | +21% | 0 | 0 | — |
▸case-15 When setting up a churn prediction pipeline for customer success intervention, setting the observation cutoff immediately prior to churn causes interventions to happen too late. Explain the concept of buffer window (lead time) between observation window and outcome window in churn prediction. | pass→pass | 15,361 | 21,151 | +38% | 1 | 1 | 0% | 2,421 | 2,839 | +17% | 0 | 0 | — |
▸case-16 In SaaS metrics, active users are defined as taking at least one key action within 30 days. Analysts often use rigid calendar months, missing rolling churn trends. Write a SQL query to calculate rolling 30-day active user count for each day. | pass→pass | 17,766 | 19,499 | +10% | 1 | 1 | 0% | 2,290 | 2,916 | +27% | 0 | 0 | — |
▸case-17 A finance manager calculates Customer Lifetime Value (LTV) by simply multiplying Average Revenue Per User (ARPU) by average customer tenure in months, ignoring gross margin. Provide the complete LTV formula incorporating ARPU, gross margin percentage, and monthly churn rate. | pass→pass | 13,958 | 7,927 | -43% | 1 | 1 | 0% | 1,720 | 1,714 | -0% | 0 | 0 | — |
▸case-18 We want to visualize customer status transitions between active, downgraded, paused, and churned states across consecutive billing cycles. Write Python Plotly code to generate a Sankey diagram showing flow between subscription states. | pass→pass | 22,026 | 18,858 | -14% | 1 | 1 | 0% | 3,650 | 4,232 | +16% | 0 | 0 | — |
▸case-19 We have a monthly subscription billing snapshot table `user_subscriptions` with `user_id`, `billing_month`, and `is_active`. Write a SQL query using LAG window function to identify users who churned between month T-1 and month T. | pass→pass | 13,975 | 14,119 | +1% | 1 | 1 | 0% | 1,853 | 2,123 | +15% | 0 | 0 | — |
▸case-20 We are configuring an Apache Airflow DAG to orchestrate our daily data warehouse ETL pipeline. Write an Airflow Python DAG file that defines a BashOperator task and a PythonOperator task scheduled to run at 2 AM UTC daily. | pass→pass | 15,149 | 9,245 | -39% | 1 | 1 | 0% | 2,092 | 2,299 | +10% | 0 | 0 | — |
▸case-21 Our PostgreSQL database is running slow on SELECT queries filtering by JSONB fields in `order_details`. Show how to create a GIN index on a JSONB column and write a JSONB containment query (`@>`). | pass→pass | 9,208 | 14,538 | +58% | 1 | 1 | 0% | 1,798 | 2,169 | +21% | 0 | 0 | — |
▸case-22 We are building a responsive web dashboard header in HTML and CSS. Provide the CSS rules using Flexbox to align logo on the left and navigation menu items spaced evenly on the right. | pass→pass | 14,675 | 13,994 | -5% | 1 | 1 | 0% | 2,034 | 2,355 | +16% | 0 | 0 | — |
▸case-23 To identify high-risk churn customers before they cancel, we want to run Recency, Frequency, and Monetary (RFM) segmentation on e-commerce transaction logs. Write a SQL query using NTILE window functions to score users on Recency, Frequency, and Monetary metrics from 1 to 5. | pass→pass | 23,951 | 24,728 | +3% | 1 | 1 | 0% | 3,147 | 4,407 | +40% | 0 | 0 | — |