Practice SQL with
real company scenarios.

Compute each captain's total_earnings = SUM(earnings), keeping only captains whose total is not NULL (use HAVING). Then return captain_name, total_earnings, cume_dist = ROUND(CUME_DIST() OVER (ORDER BY total_earnings), 2), and bonus_tier = IIF(CUME_DIST() OVER (ORDER BY total_earnings) > 0.6, 'Bonus', 'No bonus'). Order by total_earnings descending.

For each merchant_name in razorpay_merchant_txns, compute avg_fee as the average gateway_fee across transactions where a fee genuinely applies — treating any gateway_fee of -1 as not a real fee — rounded to 2 decimal places, and total_refunded as the sum of refund_amount (treating merchants with no refunds at all as 0 instead of NULL). Return merchant_name, avg_fee, and total_refunded, sorted by merchant_name.

For each age group, calculate the percentage of time spent sending snaps vs opening snaps out of total send+open time. Round to 2 decimal places.

For each user and month in listening_stats, use LAG() to find that same user's previous month minutes_streamed (ordered by month). Treat a NULL minutes_streamed as 0 when comparing. Flag a row as churn_risk = 1 if minutes dropped by more than 50% compared to the previous month, otherwise churn_risk = 0. A user's first month in the data has no previous month to compare against, so churn_risk should be 0 for that row. Return user_name, month, minutes_streamed, previous_month_minutes, and churn_risk, sorted by user_name and month.

Using a subquery in the FROM clause, first compute for each kitchen_name in swiggy_kitchen_orders: cancelled_count (orders with order_status = 'cancelled'), total_resolved (orders where order_status is not NULL), and cancellation_rate as cancelled_count divided by total_resolved times 100, rounded to 2 decimal places. Then, in the outer query, return only the kitchens where cancellation_rate is greater than 20, with columns kitchen_name, cancelled_count, total_resolved, and cancellation_rate, sorted by cancellation_rate in descending order.

For each employee, display their name, department, latest performance rating, the previous performance rating (to track improvement), their ranking within the department, and whether their latest rating is above or below their department's average. Sort by department and ranking.

For each user, count the total number of likes they have sent to others. Show user name along with the count, sorted by count in descending order.

For each rider, calculate the total number of trips they've taken and their average fare per trip. Treat any trip with a missing (NULL) fare_amount as 0 when calculating the average, rather than excluding it. Return rider_name, total_trips, and avg_fare (rounded to 2 decimal places), sorted by avg_fare in descending order.

Find total earnings of each driver and show top 3 earning drivers per city.

For each student in mock_test_scores who has a non-NULL score, calculate their percentile as PERCENT_RANK() expressed as a percentage (0 to 100, rounded to 2 decimal places) such that the highest-scoring student ends up with a percentile near 100 and the lowest-scoring student ends up near 0 — matching how 'percentile' is normally understood ('I scored better than X% of test-takers'). Exclude Vikram, whose score is NULL, entirely. Return student_name, score, and percentile, sorted by score descending.