Practice SQL with
real company scenarios.

For the habit 'Morning Meditation', build a full calendar of all seven days from 2026-08-01 through 2026-08-07 using a recursive CTE, then LEFT JOIN it against habit_logs to find that habit's status on each day. Label each day's status as 'Completed' if completed = 1, 'Skipped' if completed = 0, and 'No Data' if there's no matching row in habit_logs at all for that day. Return day and status for all seven days, sorted by day.

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.

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.