📚 100 DAYS SQL

CODING CHALLENGE

DAY 21
Master SQL One Problem at a Time

✨ Daily SQL Problems
✨ Real-World Scenarios
✨ Complete Solutions
✨ Interview Ready

📊 Download Practice Dataset

Click this link to get the CSV files:

Get Dataset (4 tables: subscriptions, users, plans, payment_history)

📦 Amazon Subscription Churn & Renewal Analysis

Amazon's subscription team needs comprehensive churn analysis to reduce cancellations, optimize pricing, and predict renewal patterns.

Table 1: subscriptions

subscription_id user_id plan_id start_date end_date status
SUB001 U1001p012023-01-152024-01-15active
sub002 u1002 P0215/06/2023NULLCANCELLED
SUB003U1003p012023-03-202024-03-20Active
Sub004u1004 P03 2023/07/1010-01-2024expired

Table 2: users

user_id user_name country
U1001alice johnsonUSA
u1002 BOB SMITHuk
U1003 Carlos Martinez SPAIN
u1004DIANA CHENchina

Table 3: plans

plan_id plan_name price_monthly billing_cycle
p01Prime Monthly14.99monthly
P02 PRIME ANNUAL139.00yearly
p03Student Prime7.49MONTHLY
P04family plan19.99Monthly

Table 4: payment_history

payment_id subscription_id payment_date amount payment_status
PAY001 SUB001 2023-01-1514.99success
pay002sub00215/06/2023139.00FAILED
PAY003SUB0032023-03-2014.99Success

Download full dataset above (30+ rows per table)

📋 Challenge Requirements

Build comprehensive churn analysis system for Amazon's subscription business.

🧹 Data Cleaning & Standardization:
  • Standardize all IDs: uppercase, trim whitespace
  • Clean user names: proper case
  • Standardize dates: convert all formats to YYYY-MM-DD
  • Handle NULL end_dates (ongoing subscriptions)
  • Standardize status values: uppercase
  • Handle NULL payment amounts
🔗 Complex Multi-Table Analysis:
  • JOIN subscriptions → users → plans → payment_history
  • Calculate subscription_duration_days for each user
  • Identify churned users (cancelled/expired vs active)
  • Calculate total_revenue_per_user across all subscriptions
  • Detect payment failures leading to cancellations
  • Calculate monthly recurring revenue (MRR) by plan
⚠️ Advanced Churn & Renewal Metrics:
  • Calculate churn_rate percentage by plan type
  • Identify at-risk users (active but with payment failures)
  • Find renewal_rate for users who completed one cycle
  • Calculate average_subscription_lifetime by country
  • Segment users: loyal (2+ renewals), trial (1 sub), churned
  • Calculate revenue_loss from churned subscriptions
  • Identify best-performing plan by retention rate

💡 CASE, DATEDIFF, COALESCE, Multi-JOINs, GROUP BY, Subqueries!

🎉 Ready to Solve?

Download the PDF for complete solution with hints!

🔗 Connect & Stay Updated

Follow my SQL journey:

LinkedIn GitHub Access all 100 challenges!

💪 Keep Practicing!

Master subscription analytics
Build churn prediction models
Analyze retention with SQL

Day 22 coming tomorrow!

Rahul Kanche — exploring data, decoding clarity!