{"name":"SQL Analyst OpenEnv","version":"2.0.0","tasks":{"1":{"difficulty":"easy","description":"Find the total number of completed orders placed in the year 2024. Return a single number with column name: total_orders"},"2":{"difficulty":"medium","description":"Find the top 5 customers by total revenue (sum of total_amount for completed orders only). Return columns: first_name, last_name, total_revenue. Order by total_revenue descending."},"3":{"difficulty":"hard","description":"For each product category, calculate the total revenue (completed orders only) and rank categories by revenue using a window function. Return columns: category, total_revenue, revenue_rank. Order by revenue_rank ascending."},"4":{"difficulty":"medium","description":"Find the average price of products in each category, but only for categories that have more than 2 products. Return columns: category, avg_price."},"5":{"difficulty":"hard","description":"Identify customers who have ordered products from both the Electronics and Clothing categories. Return columns: customer_id, first_name."},"6":{"difficulty":"expert","description":"Calculate the month-over-month revenue growth percentage for completed orders in 2024. For each month show total revenue and percentage change vs previous month. Return columns: month, total_revenue, prev_revenue, growth_pct. Order by month ascending. Round growth_pct to 2 decimal places. For the first month, prev_revenue and growth_pct should be NULL."},"7":{"difficulty":"expert","description":"For each city, find the single best-selling product by total quantity sold from completed orders. Return columns: city, product_name, total_quantity. Order by city ascending. If two products tie, return the one with the lower product_id."},"8":{"difficulty":"expert","description":"Find customers whose total spending in the second half of 2024 (July-December) was strictly greater than their total spending in the first half of 2024 (January-June). Only consider completed orders. Return columns: customer_id, first_name, last_name, h1_revenue, h2_revenue. Order by h2_revenue descending."}},"endpoints":["/reset","/step","/state","/health"]}