50 PostgreSQL Query Challenges to Master Database Skills
Level up your PostgreSQL expertise with these real-world challenges based on an educational web portal. From basic SELECT statements to complex window functions and JSON operations – this comprehensive list will transform you from beginner to expert!
Database Schema Overview :
-- Core Tables for Our Educational Portal
Users (user_id SERIAL PRIMARY KEY, username VARCHAR(50), email VARCHAR(100),
country_code CHAR(2), signup_date DATE, last_login TIMESTAMP,
subscription_tier VARCHAR(20), is_active BOOLEAN, metadata JSONB)
Courses (course_id SERIAL PRIMARY KEY, title VARCHAR(200),
instructor_id INT, category_id INT, price DECIMAL(10,2),
created_at TIMESTAMP, published BOOLEAN, difficulty_level VARCHAR(20),
tags TEXT[], avg_rating DECIMAL(3,2), enrollment_count INT)
Enrollments (enrollment_id SERIAL PRIMARY KEY, user_id INT, course_id INT,
enrolled_at TIMESTAMP, completed_at TIMESTAMP,
current_progress DECIMAL(5,2), certificate_issued BOOLEAN)
Categories (category_id SERIAL PRIMARY KEY, category_name VARCHAR(50),
parent_category_id INT, description TEXT)
Instructors (instructor_id SERIAL PRIMARY KEY, name VARCHAR(100),
bio TEXT, hire_date DATE, is_verified BOOLEAN,
total_students INT, social_links JSONB)
Lessons (lesson_id SERIAL PRIMARY KEY, course_id INT, title VARCHAR(200),
content_type VARCHAR(20), duration_minutes INT,
is_preview_allowed BOOLEAN, sort_order INT)
User_Progress (progress_id SERIAL PRIMARY KEY, user_id INT, lesson_id INT,
started_at TIMESTAMP, completed_at TIMESTAMP,
time_spent_minutes INT, quiz_score DECIMAL(5,2))
Payments (payment_id SERIAL PRIMARY KEY, user_id INT, course_id INT,
amount DECIMAL(10,2), payment_date TIMESTAMP,
payment_method VARCHAR(30), status VARCHAR(20))
Reviews (review_id SERIAL PRIMARY KEY, user_id INT, course_id INT,
rating INT CHECK (rating >= 1 AND rating <= 5), comment TEXT,
created_at TIMESTAMP, helpful_count INT)
Forums (forum_id SERIAL PRIMARY KEY, course_id INT, user_id INT,
title VARCHAR(200), content TEXT, created_at TIMESTAMP,
is_resolved BOOLEAN, view_count INT)
Forum_Replies (reply_id SERIAL PRIMARY KEY, forum_id INT, user_id INT,
content TEXT, created_at TIMESTAMP, is_solution BOOLEAN)
50 PostgreSQL Query Challenges
Level 1: Foundation Queries
1. Find all active users from Germany who signed up in 2024.
2. List all published courses with average rating above 4.5, sorted by enrollment count.
3. Show courses created in the last 7 days using CURRENT_DATE.
4. Find instructors hired before 2022 who have taught more than 1000 students.
5. Count users by subscription tier and country.
6. Find duplicate email addresses in the users table.
7. List courses with their categories (including parent categories if they exist).
8. Show all lessons for a specific course, ordered by sort_order.
9. Find users who haven't logged in for more than 90 days.
10. Calculate total revenue from payments made in the current month.
11. Show courses with no enrollments yet.
12. List users who enrolled in a course but never started any lesson.
Level 2: Intermediate Analytics
13. Find the average course completion percentage per user.
14. Show the most popular course categories by enrollment count.
15. Calculate monthly user signup trends for the past year.
16. Find courses where the actual enrollment count doesn't match enrollment_count column.
17. Show instructors along with their total course revenue.
18. Identify users who completed a course within 7 days of enrollment.
19. Find the longest lesson in each course (by duration).
20. Calculate the average time spent per lesson for each user.
21. Show courses with decreasing enrollment trends month-over-month.
22. Find users who have reviewed every course they enrolled in.
23. Calculate instructor retention rate (users who take multiple courses from same instructor).
24. Identify peak learning hours based on lesson start times.
Level 3: Advanced Joins & CTEs
25. Using a CTE, find the second most popular course in each category.
26. Show users who have achieved "perfect scores" (100% on all quizzes).
27. Find "power users" - those in the top 10% by total learning time.
28. Calculate the running total of enrollments per course over time.
29. Show the learning path of users (course progression order).
30. Find courses that have both beginner and advanced lessons.
31. Using a self-join, find users who share the same learning patterns.
32. Show the longest learning streak for each user.
33. Find "learning clusters" - users who take similar sets of courses.
34. Calculate the churn rate (users who don't enroll in new courses).
35. Show the most helpful reviews per course (using window functions).
36. Find "bridge courses" that lead to enrollment in more advanced courses.
Level 4: JSON & Array Operations
37. Extract all unique tags from the courses table's tags array.
38. Find courses that have specific tags in their tag array.
39. Update a user's metadata JSONB field with new learning preferences.
40. Query users' social links from the metadata JSONB field.
41. Find courses with 3 or more tags using array length.
42. Aggregate all tags used across all courses with their frequencies.
Level 5: Window Functions & Analytics
43. Rank courses within each category by revenue.
44. Calculate the 7-day moving average of daily enrollments.
45. Show the percentage of total revenue each course contributes.
46. Find the median course price per category.
47. Calculate cumulative completion percentage per user per course.
48. Show the difference in rating from the course average for each review.
Level 6: Expert Challenges
49. Learning Prediction Query: Identify users likely to enroll in a new course based on:
- Completion rate of current courses
- Time since last enrollment
- Similar users' behavior patterns
- Course category preferences
50. Platform Health Dashboard Query : Create a comprehensive query that returns :
- Daily Active Users (DAU) vs Monthly Active Users (MAU) ratio
- Course completion funnel metrics
- Revenue per user segmentation
- Instructor performance scores
- Forum engagement levels
Bonus: Real Business Scenarios
A. Personalization Engine :
Write a query that recommends 3 courses to each user based on :
- Courses similar users enrolled in
- Complementary skills to their completed courses
- Popular courses in their timezone
- Courses with high satisfaction ratings
B. Pricing Optimization:
Analyze which price points lead to :
- Highest conversion rates
- Best completion rates
- Most repeat enrollments
- Highest satisfaction scores
C. Instructor Success Formula :
Calculate an instructor success score considering :
- Student completion rates
- Course ratings
- Forum engagement
- Student retention to next course
PostgreSQL-Specific Features to Use :
CTEs (WITH clauses) for complex queries
Window Functions (ROW_NUMBER, RANK, LAG, LEAD)
JSONB operators (->, ->>, @>, ?)
Array functions (ANY, ALL, ARRAY_LENGTH, UNNEST)
Date/Time functions with time zones
Full-text search on course descriptions
GIS extensions for location-based queries (PostGIS)
Materialized Views for performance
Common Table Expressions for recursive queries
Sample Advanced Query Template :
-- Example: Find users with accelerating learning patterns
WITH user_learning AS (
SELECT
user_id,
course_id,
enrolled_at,
completed_at,
completed_at - enrolled_at AS completion_time,
LAG(completed_at - enrolled_at) OVER (
PARTITION BY user_id ORDER BY enrolled_at
) AS prev_completion_time
FROM enrollments
WHERE completed_at IS NOT NULL
)
SELECT
user_id,
AVG(completion_time) AS avg_completion_time,
CASE
WHEN AVG(completion_time) < AVG(prev_completion_time)
THEN 'Accelerating'
ELSE 'Steady or Slowing'
END AS learning_trend
FROM user_learning
GROUP BY user_id
HAVING COUNT(*) >= 3;
Challenge Submission Guidelines :
1. Share your solution in the comments
2. Explain your approach and any optimizations
3. Compare execution plans for different approaches
4. Suggest indexes that would improve performance
5. Tag your PostgreSQL version (14, 15, 16)