Practice SQL with
real company scenarios.

Find every pair of reservations for the same listing whose stay dates genuinely overlap — not reservations that simply share a listing, and not back-to-back turnovers where one checkout date equals the next check-in date. Treat a NULL check_out as an open-ended stay that hasn't ended yet (it overlaps with any later reservation on that listing). Return each overlapping pair once (not twice, and never a reservation paired with itself) as listing_id, guest_1, check_in_1, check_out_1, guest_2, check_in_2, check_out_2, sorted by listing_id then guest_1.

For each customer, calculate the total number of orders and total order amount per month. Treat any NULL order_amount as 0. Return customer_id, order_month (in YYYY-MM format), total_orders, and total_amount, sorted by customer_id and then order_month.

For each user in learning_activity, calculate total_xp as the sum of xp_earned (treating a NULL xp_earned, from a streak-freeze day, as 0). Calculate avg_xp_per_day as the average xp_earned across all logged days for that user, also treating NULL as 0 — a freeze day should pull the average down, not be excluded from it. Calculate language_count as the number of distinct languages the user has activity for, and languages_list as those distinct language names joined into a single comma-separated string, sorted alphabetically. Return user_name, total_xp, avg_xp_per_day (rounded to 2 decimal places), language_count, and languages_list, sorted by total_xp in descending order.

Find suppliers whose average product unit price is greater than the average unit price of all suppliers in the same category. Return the supplier name, category, and average unit price. Sort the results by category and then by average unit price in descending order.

For each user in portfolio_snapshots, ignoring any row where portfolio_value is NULL, find their first_value (portfolio_value on their earliest snapshot_date) and last_value (portfolio_value on their most recent snapshot_date). Calculate growth_amount as last_value minus first_value, and growth_pct as that difference as a percentage of first_value, rounded to 2 decimal places. Return one row per user_name with first_value, last_value, growth_amount, and growth_pct, sorted by growth_pct in descending order.

For each reseller, calculate net revenue as the total value of delivered orders minus the total value of RTO (returned) orders. Only include resellers with at least 5 total orders. Rank resellers within their product category by net revenue, using DENSE_RANK so tied resellers share a rank. Also calculate the running total of net revenue within each category, ordered by rank (and by reseller name to break ties consistently). Return reseller_name, category, total_orders, net_revenue, category_rank, and running_net_revenue, sorted by category, then rank, then reseller name.

Find products whose total sales quantity is greater than the average total sales quantity of products from the same brand. Return the product name, brand and total sales quantity. Sort the result by brand and total sales quantity in descending order.

For each TV show, calculate the completion percentage based on users who started Episode 1 versus users who watched the final episode. Round the result to 2 decimal places.

Rank customers into four loyalty tiers based on total_spend, using NTILE(4) ordered by total_spend descending, so the top-spending quarter becomes tier 1. Exclude any customer with a NULL total_spend entirely — they haven't made a purchase yet and shouldn't be tiered. Label tier 1 as 'Platinum', tier 2 as 'Gold', tier 3 as 'Silver', and tier 4 as 'Bronze'. Return customer_name, total_spend, and loyalty_tier, sorted by total_spend descending.

The trips table sometimes has multiple rows for the same trip_id, logged because of app retries. For each trip_id, keep only the row with the most recent updated_at and discard the older duplicate rows. Return trip_id, driver_id, rider_id, fare, and updated_at for the deduplicated trips, sorted by trip_id.