Learn SQL by Doing: 100+ Practical Challenges

Query the users table to list active administrators, filtering by role = 'admin' and status = 'active', and select user ID and name using MySQL Workbench.

Walk through building a viewer plan mapping with an inner join between users and subscriptions, retrieving subscription id, full name, and subscribed on date.

Walk through returning each user's top three most viewed articles with sql. Learn to use a cte, window functions, and row_number with partition by user and order by views.

Walk through unifying creators and moderators into a single result set with joins and union options, selecting name and role in MySQL.

Pivot daily ticket status data by day to count open, resolved, and escalated tickets, using case when labels and group by status_date in a practical SQL walkthrough.

Learn to compute the average days between order and ship dates per region in MySQL. Join orders and shipments, calculate date differences, and group results by region to identify bottlenecks.

Compute the conversion rate for each campaign from a campaign interactions table by counting purchases and total users reached, then calculate campaign effectiveness with optional rounding.

Demonstrate a daily breakdown of new versus returning users by joining users and user logins, comparing sign up date to login date, and counting per login date in MySQL.

Analyze the App Sessions data to identify each app's most active hour using MySQL, counting sessions and ranking peak hours with window functions (partition by app name, order by sessions).

Identify top active users by counting transactions in the last full calendar month. Group by user_id and sort by the transaction count to highlight top activity.

Identify users who logged in from two or more cities on the same day by aggregating by user and date, counting distinct cities, and applying a having clause to flag.

Identify high-intent shoppers who added more than two items in a day but never placed an order, using cart items and orders tables, via an anti-join approach.

Learn to identify unbooked room types by joining listings with bookings using a left join, then group by room type to count listings and show a sample listing.

Identify the latest valid sale for each active team (completed, at least 500, by an active employee) and show all active teams with nulls when no sale exists.

Learn to compute the lowest movie price by applying eligible discounts via a cross join of movies and offers, filtering by the offers' minimum required price, and selecting best price.

Follow a solution walkthrough for detecting the first overlapping subscription per user in sql, using a self-join and window functions to select the later start date.

Identify the top three products per section by units sold, including ties for third place, using a window function rank with partition by section in MySQL Workbench via a subquery.

Follow a solution walkthrough to compute the salary difference using a lag window function over the salary history, partitioned by employee and ordered by update date, to reveal previous salary.

Learn to compute daily unique visits and a seven-day rolling average with window functions, using range between to handle date gaps and contrasting with self-join approaches.

Identify each user's longest posting streak of at least three consecutive days, returning the start and end dates, using row_number and streak keys to group consecutive days in SQL.

Identify binge versus sporadic viewing by calculating days since each user's last watch using lag and date diff in SQL, and label sessions accordingly.

Identify user IDs registered in the current month from the users table using year and month comparisons with the given date 2020-03-20.

Create a monthly enrollment report from the admissions table by using date_format to year and month, then group by that value and count records in chronological order.

Analyze weekend vs weekday signups and revenue using a weekday function in MySQL, labeling days as weekday or weekend and aggregating total signups and subscription fees.

Discover how to compute the days between a user's first and second book orders, for users with at least two purchases, using partition by user ID and date difference.

Identify gaps between patient visits using SQL; highlight cases exceeding 180 days and return patient ID, previous visit, current visit, and the gap in days via lag.

Demonstrate how to identify tickets with delays over 24 hours using timestamp diff in MySQL, returning ticket id, user id, created timestamp, response timestamp, and delay in hours.

Learn how to return the latest appointment per patient by using a window function in MySQL Workbench, partitioned by patient id and ordered by appointment date, with rank = 1.

Identify deal hunter customers by joining users, orders, and products to find users with at least one order where every product is discounted, using group by and case when.

Identify customers with a return rate over 50% by aggregating total orders and total returns from orders and order returns tables, then compute the return percentage in MySQL.

Compute each influencer's total follower purchases by joining influencers, followers, and purchases, sum the amounts, and return the influencer id with total earnings for totals of 1000 or more.

Identify users with a failed auto-renewal who manually renewed within 1 to 7 days by joining the subscription_failures and manual_renewals tables.

Identify leads that move from new lead to qualified candidate within seven days and report lead id, the new lead date, the qualified date, and days to convert.

Learn to compute recency, frequency, and monetary values from an orders table over 90 days, and segment customers into champions, loyal, and at risk using a case-based labeling approach.

learn sql by doing with a solution walkthrough that counts gender groups, handles nulls as unspecified, and standardizes values using case when and group by.

Join the movie orders and ship DVD shipments tables to identify mismatches between ordered and shipped movie IDs, then compute per-center total orders, mismatches, and mismatch rate.

Identify underperforming trips by computing each vehicle's average fuel efficiency and flagging trips below 70% of that average using sql queries.

Generate a tag summary for each product by aggregating all associated tags into a comma separated string using group_concat, with group by and order by to prepare display and analytics.

Learn to compute an employee hierarchy depth with a top-down recursive sql query, starting at top-level managers (depth zero) and propagating depths to all reports.

Learn to roll up salaries in an organization chart by aggregating direct and indirect reports with a recursive query, union all, and a top-down approach.

Discover a solution walkthrough that combines clients and freelancers with union all, then labels each user as client, freelancer, or both, outputting a unified name and role list.

Walks through identifying users who bought two or more distinct companies but have missing stop-loss alerts, by joining buy orders with alert settings to count missing alerts.

Identify users active on both website and app in the last 60 days, listing their user IDs, latest website login, latest app login, and total platform logins.

Identify users who completed only one of two actions—view or apply—by joining view and apply tables in sql, returning user IDs with action dates using anti joins or left joins.