📚 100 DAYS SQL
CODING CHALLENGE

DAY 15

Government Welfare & Subsidy Management

✨ Master SQL One Problem at a Time ✨

📊 Download Practice Dataset

Get all CSV files to practice this challenge:

📥 Download Day 15 Data

4 tables: citizens, income_brackets, subsidies, subsidy_applications

🏛️ Government Welfare System

Build a system to validate government subsidies, detect fraud, and manage citizen benefits.

Table 1: citizens (5 of 25 rows)

citizen_id full_name date_of_birth address profession annual_income family_size disability_status
C001 rajesh kumar 15/03/1980 mumbai, maharashtra FARMER 45000 5 NO
c002 PRIYA SHARMA 1985-07-22 Delhi teacher NULL 3 yes
C003 amit patel 10-05-1992 ahmedabad, GUJARAT Engineer 850000 2 No
C004 sunita devi 1975/12/08 patna,bihar NULL 25000 7 YES
c005 Mohammed Ali 22-11-1988 Hyderabad, Telangana daily wage worker 72000 4 no

...20 more rows in the download!

Table 2: income_brackets

bracket_id bracket_name min_income max_income priority_level
B1Below Poverty Line050000HIGH
B2Low Income50001150000MEDIUM
B3Middle Income150001500000LOW
B4High Income500001999999999NONE

Table 3: subsidies

subsidy_id scheme_name category amount min_family_size requires_disability max_income
S001Ration Card SubsidyFood Security120001NO50000
S002Disability PensionSocial Welfare240001YES150000
S003Farmer Support SchemeAgriculture300003NO150000
S004Education GrantEducation150002NO200000
S005Housing SubsidyHousing500004NO100000

Table 4: subsidy_applications

application_id citizen_id subsidy_id application_date status
APP001C001 S001 2024-01-15APPROVED
APP002c002s00215/01/2024pending
APP003C003S0012024/01/20REJECTED
APP004C004s00520-01-2024Approved
APP005c001S0032024-01-22APPROVED

📋 Your Challenge

Build a government welfare management system with data cleaning, eligibility validation, and fraud detection.

🧹 Data Cleaning:
  • Standardize all IDs (citizen_id, subsidy_id) - uppercase and trim
  • Clean names and addresses - proper case formatting
  • Standardize all date formats to YYYY-MM-DD
  • Handle NULL income values (use median estimate)
  • Standardize profession names - proper case
  • Standardize disability status - YES/NO uppercase
🔗 JOINs & Data Enrichment:
  • JOIN citizens to income_brackets using BETWEEN
  • JOIN applications to citizens
  • JOIN applications to subsidies
  • Detect orphan applications (invalid citizen_id or subsidy_id)
  • Calculate citizen age from date of birth
  • Assign income bracket and priority level to each citizen
⚠️ Eligibility Validation & Fraud Detection:
  • Validate income eligibility (citizen income ≤ subsidy max income)
  • Validate family size requirement (family size ≥ minimum required)
  • Validate disability requirement (if required, citizen must have it)
  • Flag approved applications that don't meet eligibility criteria
  • Calculate fraud risk score (0-145 points)
  • Categorize risk level: High Risk (51+), Medium Risk (21-50), Low Risk (0-20)
  • Determine final eligibility status: Eligible or Ineligible

💡 Key SQL Concepts: BETWEEN, CASE WHEN, COALESCE, LEFT JOIN, TIMESTAMPDIFF, CTEs, Complex Logic!

🎯 Ready to Solve?

Download the full challenge PDF with complete solution, explanations, and step-by-step hints!

🔗 Connect & Get Solutions

Follow for complete solutions and all 100 challenges:

Complete solutions posted on LinkedIn • All data on GitHub

💪 Keep Practicing!

Master government data pipelines
Build subsidy validation systems
Detect fraud with SQL

Day 16 coming tomorrow!

Follow the 100 Days SQL Challenge

Master SQL one problem at a time!

Rahul Kanche — exploring data, decoding clarity!