Work through 70 advanced SQL exercises in a structured sequence. Each challenge includes a clear problem, schema context and an interactive query editor.
Rank employees by salary within each department using RANK(), then return each employee's department, salary, and position. Return columns: first_name, last_name, department_id, salary, salary_rank. Order results by: salary DESC) AS salary_rank FROM employees ORDER BY department_id, salary_rank.
Calculate a chronological running total of order amounts and return every order with its date, amount, and cumulative revenue. Return columns: id, order_date, total_amount, running_total. Order results by: order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM orders ORDER BY order_date.
Using a CTE, find the highest-paid employee in each department. Return columns: department_id, first_name, last_name, max_salary. Order results by: e.department_id.
Summarize monthly order volume and revenue, then compare each month with the previous month using LAG(). Return columns: month, orders, revenue, prev_revenue, revenue_change. Order results by: month) AS prev_revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS revenue_change FROM monthly ORDER BY month.
Divide employees into four salary quartiles with NTILE(4), then return each employee's name, salary, and quartile. Return columns: first_name, salary, quartile. Order results by: salary DESC) AS quartile FROM employees ORDER BY salary DESC.
Use DENSE_RANK() to rank products by price inside each category while keeping equal prices at the same rank. Return columns: name, category, price, price_rank. Order results by: price DESC) AS price_rank FROM products ORDER BY category, price_rank, name.
Number each customer's orders chronologically with a window function and return the customer, order date, and sequence number. Return columns: customer_id, order_id, order_date, order_number. Order results by: order_date, id) AS order_number FROM orders ORDER BY customer_id, order_number.
Use a CTE to return the three highest-paid employees. Return columns: first_name, last_name, salary, salary_rank. Order results by: salary DESC) AS salary_rank FROM employees) SELECT first_name, last_name, salary, salary_rank FROM ranked WHERE salary_rank <= 3 ORDER BY salary_rank.
Calculate each employee's percentage share of their department payroll. Return columns: first_name, department_id, salary, payroll_share. Order results by: department_id, salary DESC.
Compare every order amount with the previous chronological order using LAG(), while preserving the complete order sequence. Return columns: id, order_date, total_amount, previous_amount. Order results by: order_date, id) AS previous_amount FROM orders ORDER BY order_date, id.
Return customers who placed at least 1 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 2 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 3 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 4 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 5 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 6 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 7 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Return customers who placed at least 8 orders. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Find customers whose total completed-order spend exceeds 250. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 500. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 750. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 900. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 1200. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 1500. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 1800. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
Find customers whose total completed-order spend exceeds 2500. Return columns: customer_name, total_spend. Order results by: total_spend DESC, customer_name.
List order items with quantity at least 1, including the product name. Return columns: order_id, product_name, quantity, unit_price. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 2, including the product name. Return columns: order_id, product_name, quantity, unit_price. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 3, including the product name. Return columns: order_id, product_name, quantity, unit_price. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 4, including the product name. Return columns: order_id, product_name, quantity, unit_price. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 5, including the product name. Return columns: order_id, product_name, quantity, unit_price. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 6, including the product name. Return columns: order_id, product_name, quantity, unit_price. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 7, including the product name. Return columns: order_id, product_name, quantity, unit_price. Order results by: oi.quantity DESC, oi.id.
List order items with quantity at least 8, including the product name. Return columns: order_id, product_name, quantity, unit_price. Order results by: oi.quantity DESC, oi.id.
Summarize order statuses whose total revenue is above 250. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 500. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 750. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 900. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 1200. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 1500. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 1800. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Summarize order statuses whose total revenue is above 2500. Return columns: status, order_count, total_revenue. Order results by: total_revenue DESC, status.
Count each customer's orders placed on or after 2024-01-01. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-01-12. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-01-20. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-02-01. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-02-15. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-03-01. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-03-08. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
Count each customer's orders placed on or after 2024-03-12. Return columns: customer_name, order_count. Order results by: order_count DESC, customer_name.
For each marketplace, return active sellers that offer at least one active listing in every category required by that marketplace. Ignore marketplaces with no requirements. Return marketplace_id and seller_id ordered by both columns. Return columns: marketplace_id, seller_id.
Find canonical product pairs appearing together in at least two distinct completed baskets. Repeated lines for a product in one basket count once. Return product_a, product_b and basket_count, ordered by basket_count descending then product IDs. Return columns: product_a, product_b, basket_count.
Return every pair of subscriptions for the same customer whose inclusive active periods overlap. A NULL end_date means the subscription is still active. Return customer_id, subscription_a, subscription_b, overlap_start and overlap_end; use NULL overlap_end for an open-ended overlap. Return columns: customer_id, subscription_a, subscription_b, overlap_start, overlap_end.
For every order, select the customer's address version valid at ordered_at. Validity is inclusive at valid_from and exclusive at valid_to; NULL valid_to is open-ended. If versions share valid_from, choose the highest address_id. Return order_id, address_id and city. Return columns: order_id, address_id, city.
Show the complete escalation ownership for open tickets: assigned agent, that agent's team lead, and the lead's support director. Exclude tickets whose chain is incomplete. Return ticket_id, agent_name, lead_name and director_name. Return columns: ticket_id, agent_name, lead_name, director_name.
Find canonical pairs of active sales representatives who currently share at least two distinct customers. Duplicate assignment history must not inflate the count. Return rep_a, rep_b and shared_customers. Return columns: rep_a, rep_b, shared_customers.
For each non-empty product category, return customers who bought every active product in that category through completed purchases. Multiple purchases of the same product count once. Return category and customer_id. Return columns: category, customer_id.
An activity is a login, purchase, or support event in customer_activity. A return event is re-engagement when it occurs at least 90 full days after that customer's immediately previous event; exactly 90 days qualifies. Return customer_id, return_event_id, return_at, previous_activity_at and inactivity_days. Return columns: customer_id, return_event_id, return_at, previous_activity_at, inactivity_days.
For each customer, identify the first completed purchase and the first completed purchase after it. Same-time purchases are sequenced by purchase_id. Return customers whose first repeat occurs within 30 calendar days inclusive, with first_purchase_id, repeat_purchase_id and days_to_repeat. Cancelled purchases do not count. Return columns: customer_id, first_purchase_id, repeat_purchase_id, days_to_repeat.
Rank products within each category by total completed-sales revenue. Rank 1 is the highest distinct revenue; products tied at the third distinct revenue are all included. Return category, product_id, revenue and revenue_rank ordered by category, rank, product_id. Return columns: category, product_id, revenue, revenue_rank.
Showing the first 60 of 70 challenges in this learning path.
Browse and filter all challenges in the Practice ArenaLevel guide
Advanced SQL challenges emphasize analytical reasoning with window functions, multi-step transformations and realistic reporting logic.