📚 100 DAYS SQL
CODING CHALLENGE

DAY 19

Airline Flight Delays & Aircraft Utilization

✨ Master SQL One Problem at a Time ✨

📊 Download Practice Dataset

Get all CSV files to practice this challenge:

📥 Download Day 19 Data

3 tables: flights, aircraft, routes

✈️ Airline Operations Analytics

Analyze flight delays, aircraft performance, and maintenance tracking for optimal airline operations.

Table 1: flights (5 of 25 rows)

flight_id aircraft_id route_id flight_date delay_minutes status
FL001 A101R0012024-01-150ON TIME
fl002A102 r002 2024-01-1515delayed
FL003A101R0032024-01-1645DELAYED
fl004 A103 R0012024-01-160On Time

...20 more rows in the download!

Table 2: aircraft

aircraft_id model manufacturer seat_capacity last_maintenance
A101737-800Boeing1892024-01-10
A102A320Airbus180NULL
A103737-900Boeing2202024-01-05

Table 3: routes

route_id origin destination distance_km
R001MumbaiDelhi1400
R002DelhiBangalore1740
R003MumbaiBangalore980

📋 Your Challenge

Build airline operations analytics with data cleaning, delay calculations, and performance scoring.

🧹 Data Cleaning:
  • Standardize all IDs (flight_id, aircraft_id, route_id) - uppercase and trim
  • Standardize status values - uppercase
  • Handle NULL actual_departure (cancelled flights)
  • Calculate delays from timestamps when delay_minutes is NULL
  • Handle NULL last_maintenance dates
🔗 JOINs & Data Enrichment:
  • JOIN flights to aircraft
  • JOIN flights to routes
  • Calculate aircraft age from year_manufactured
  • Calculate days since last maintenance
  • Detect orphan flights (invalid aircraft_id or route_id)
⚠️ Performance Analysis:
  • Calculate delay rate percentage per aircraft
  • Calculate average delay for delayed flights only
  • Identify high-delay aircraft (>30% delayed flights)
  • Flag aircraft overdue for maintenance (>30 days)
  • Categorize performance: Critical, Warning, Good, Excellent

💡 Key SQL Concepts: TIMESTAMPDIFF, DATEDIFF, CASE WHEN, LEFT JOIN, CTEs, Conditional Aggregation!

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 airline operations data
Build delay analytics systems
Optimize aircraft utilization

Day 20 coming tomorrow!

Follow the 100 Days SQL Challenge

Master SQL one problem at a time!

Rahul Kanche --- exploring data, decoding clarity!

Created by: Rahul Kanche