25 Database Query Challenges to Test & Improve Your SQL Skills
Dataset Overview :
Assume a simple e-learning platform database with these main tables:
Users (user_id, name, email, join_date, country, subscription_type)
Courses (course_id, title, instructor_id, category, price, created_date)
Enrollments (enrollment_id, user_id, course_id, enrollment_date, completion_percentage)
Instructors (instructor_id, name, specialization, hire_date)
Assignments (assignment_id, course_id, title, due_date, max_score)
Submissions (submission_id, user_id, assignment_id, score, submission_date)
25 Query Challenges :
1. First Steps:
List all users who joined in 2024, sorted by join date (newest first).
2. Free Learners:
Find all users with 'Free' subscription type from Canada.
3. Course Catalog:
Show all courses in the 'Data Science' category, priced under $100.
4. Instructor Intro:
Display instructors hired before 2022, sorted by specialization.
5. Simple Count:
How many courses does each instructor teach? Show instructor name and course count.
6. Completion Check:
Find users who have completed (100%) any course.
7. Late Starters:
List courses created in the last 3 months but with no enrollments yet.
8. Geography Lesson:
Count users by country, showing only countries with more than 50 users.
9. Popular Courses:
Show the top 5 most enrolled courses (by enrollment count).
10. High Achievers:
Find users who scored above 90% on any assignment.
11. Revenue Report:
Calculate total revenue per course category (sum of all course prices for enrolled users).
12. Inactive Detect:
List users who enrolled in a course but have 0% completion after 30 days.
13. Busy Instructors:
Show instructors teaching more than 3 courses, along with their average course price.
14. Streak Finders:
Find users who enrolled in at least 2 courses on the same day.
15. Progression Check:
For each course, show the average completion percentage across all enrolled users.
16. Price Analysis:
Find courses priced higher than the average price of all courses in their category.
17. Submission Timing:
Calculate the average submission time (days before due date) per assignment.
18. Learning Path:
Find users who completed a beginner course before enrolling in an advanced course of the same category.
19. Revenue Trends:
Show monthly revenue trends for 2024 (month by month).
20. Churn Risk:
Identify users who haven't submitted any assignment in the last 30 days despite being enrolled.
21. Category Cross:
Find users enrolled in courses from at least 3 different categories.
22. Performance Correlation:
Do users with higher assignment scores have higher course completion percentages? (Return average score vs completion for analysis).
23. Learning Gaps:
Find assignments where the average score is below 60%, and list the enrolled users who haven't submitted them yet.
24. Instructor Impact:
Compare the average completion percentage of courses taught by instructors hired in the last year vs. veterans.
25. Predictive Query:
(Hypothetical) If users who complete 80% of a course within 30 days are 5x more likely to purchase another course, identify current users in this "high potential" segment.
Real-World Scenario :
The Business Ask: "We want to offer personalized course recommendations. Write a query that, for each user, suggests courses:
- In categories they're already enrolled in
- Taught by different instructors than their current courses
- Priced below their historically paid average (or free)
- With at least 100 other enrolled users for social proof"
Pro Tips :
- Always test with sample data first
- Use CTEs (Common Table Expressions) for complex queries
- Consider performance with large datasets
- Comment your logic for complex joins
- Validate results with spot checks
Challenge Submission :
Post your solutions in the comments!
- Share your most elegant query
- Explain a tricky join you solved
- Show a before/after optimization